Microsoft SQL Server forum - Getting data from multiple rows into one column

Author

Getting data from multiple rows into one column

bika

2005-12-02, 8:24 pm

I have a table that has values as follows:
PersonID Degree
55 MD
55 Phd
55 RN
60 MD
60 Phd

I need a create a query that will give me output like this:

PersonID Degree
55 MD, Phd, RN
60 MD, Phd

Any ideas

Erland Sommarskog

2005-12-03, 7:23 am

bika (aesahab@gmail.com) writes:
> I have a table that has values as follows:
> PersonID Degree
> 55 MD
> 55 Phd
> 55 RN
> 60 MD
> 60 Phd
>
> I need a create a query that will give me output like this:
>
> PersonID Degree
> 55 MD, Phd, RN
> 60 MD, Phd

If you are on SQL 2000, you will have to run a cursor. There is no defined
way to produce this result set. (There are some undefined ways which may
work, but I would not recommend to rely on.)

If you are on SQL 2005, this is possible thanks to the improved XML support.
I got this example from an SQL Server developer:

select CustomerID,
substring(OrdIdList,
1, datalength(OrdIdList
)/2 - 1)
-- strip the last ',' from the list
from
Customers c cross apply
(select convert(nvarchar(30)
, OrderID) + ',' as [text()]
from Orders o
where o.CustomerID = c.CustomerID
order by o.OrderID
for xml path('')) as Dummy(OrdIdList)
go

I have not really grasped how it works, but it works. :-)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

gjvdkamp@gmail.com

2005-12-03, 1:23 pm

Here's another one:

If you know in advance what the different types of degrees are going to
be you can use this query:

select personid,
Min(Case when Degree = 'Md' then degree end) as 'Md',
Min(Case when Degree = 'Phd' then degree end) as 'Phd',
Min(Case when Degree = 'Rn' then degree end) as 'Rn'
from Degrees
group by PersonId

If you dont know in advance what degrees you can expect in the db, you
can use a cursor to produce the 'min(case ... end) as .., ' parts on
the fly:

declare @DegName varchar(50)
declare @Sql nvarchar(4000)

declare c cursor FAST_FORWARD for
select distinct degree from degrees order by degree

open c
fetch next from c into @DegName

set @Sql = 'select personid '
while @@Fetch_Status = 0
begin
set @Sql = @Sql + ', Min(Case when Degree = ''' + @DegName + ''' then
degree end) as ''' + @DegName + ''' '
fetch next from c into @DegName
end
close c
deallocate c
set @Sql = @Sql + ' from Degrees group by PersonId'
print @sql
exec (@sql)

Erland, i actually learned this dynamic sql from you!

Hope this helps,

Gert-Jan

Rich Ryan

2005-12-03, 8:23 pm

This problem pops up a lot in database newsgroups. If you remember, rule 1
is "no repeating groups". So to create a query that creates repeating groups
goes against the SQL model.

To do this in the most SQL way, Create a table like
CREATE TABLE PersonDegrees(
PersonID int,
IsRN char(1),
Is MD char(1),
IsPHD char(1),
....
....
IsLawyer Char(1))

Where Is...= 'Y' or 'N'

This looks likes a repeatng group, but it is not.

This way you can do queries like:
Show me people that are MDs, PHDs, and not Lawyers.

You can easily populate this table from your original M:M table.

Rich
"bika" <aesahab@gmail.com> wrote in message
news:1133571514.032416.98880@z14g2000cwz.googlegroups.com...
> I have a table that has values as follows:
> PersonID Degree
> 55 MD
> 55 Phd
> 55 RN
> 60 MD
> 60 Phd
>
> I need a create a query that will give me output like this:
>
> PersonID Degree
> 55 MD, Phd, RN
> 60 MD, Phd
>
> Any ideas
>
>

--CELKO--

2005-12-04, 3:23 am

Could you explain why you want to violate

1) The foundation of RDBMS, First Normal Form?
2) The most basic rule of a tiered architecture?

If you have a solid reason, woudl you mind publishing it, since that
would overturn 30+ yers of RDBMS and 40+ years of Comp Sci.

Erland Sommarskog

2005-12-04, 7:23 am

--CELKO-- (jcelko212@earthlink
.net) writes:
> Could you explain why you want to violate
>
> 1) The foundation of RDBMS, First Normal Form?

Because that is the way the user wants to see the data. You know plain
users does not give a dim wit about first normal forms. For them a
presentation like:

A: 2, 1, 2, 3
B: 3, 4, 5, 3

is probably a very normal form to them.

> 2) The most basic rule of a tiered architecture?

While this is best done client-side with SQL 2000, I don't think this is
something which is very well supported with report writers. And not all
clients are even that sophisticated. Many reports are run from Query
Analyzer or a similar tool with no formatting capabilities at all. Thus,
any formatting has to be done in the RBDMS.

> If you have a solid reason, woudl you mind publishing it, since that
> would overturn 30+ yers of RDBMS and 40+ years of Comp Sci.

Incidently, I have told you this several times before. So why do you keep
asking questions, when you do not listen to the answers?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Alexander Kuznetsov

2005-12-04, 1:23 pm

> The most basic rule of a tiered architecture?

The most basic rule of business is making money. Soemtimes it is way
cheaper to implement formatting just once in the database, as opposed
to doing it in VB, Crystal report, ASP, ASP.Net, whatever else.

Also note that data is transferred to the client in packets. So the
difference between sending over the network 1 packet:

Smith, John 1,3,5,7,17

and sending over the network 2 or more packets:

Smith, John 1
Smith, John 3
Smith, John 5
Smith, John 7
Smith, John 17

is at least 100% drop in performance.

Rich Ryan

2005-12-04, 8:23 pm

Joe, I'm with you on this one. These young gunners seem to think of RDMS as
a file access mechanism. Just read the MYSQL Newsgroup. BTW I'm about your
age.

"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1133687403.902376.122910@g44g2000cwa.googlegroups.com...
> Could you explain why you want to violate
>
> 1) The foundation of RDBMS, First Normal Form?
> 2) The most basic rule of a tiered architecture?
>
> If you have a solid reason, woudl you mind publishing it, since that
> would overturn 30+ yers of RDBMS and 40+ years of Comp Sci.
>

Rich Ryan

2005-12-04, 8:23 pm

But you notice in this example the number of columns is fixed. Not what the
OP wanted.

Rich
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns9722758F36C4
7Yazorman@127.0.0.1...
> --CELKO-- (jcelko212@earthlink
.net) writes:
>
> Because that is the way the user wants to see the data. You know plain
> users does not give a dim wit about first normal forms. For them a
> presentation like:
>
> A: 2, 1, 2, 3
> B: 3, 4, 5, 3
>
> is probably a very normal form to them.
>
>
> While this is best done client-side with SQL 2000, I don't think this is
> something which is very well supported with report writers. And not all
> clients are even that sophisticated. Many reports are run from Query
> Analyzer or a similar tool with no formatting capabilities at all. Thus,
> any formatting has to be done in the RBDMS.
>
>
> Incidently, I have told you this several times before. So why do you keep
> asking questions, when you do not listen to the answers?
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

Tony Rogerson

2005-12-05, 11:23 am

Rich,

So you've never had a requirement as a developer to show a comma seperated
list on the screen, i've done a lot of CRM development and that requirement
is very frequent.

Concatenating on the server scales significantly better than passing back
all the rows to the client/middle tier.

In SQL Server 2005 we can do it in one very simple statement utilising FOR
XML extensions, this makes for less code, less complexity and the logic is
coded once in a central location - do you not agree that is good?

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"Rich Ryan" <ryanrj@sbcglobal.net> wrote in message
news:OIKkf.3048$rq3.2138@newssvr19.news.prodigy.com...
> Joe, I'm with you on this one. These young gunners seem to think of RDMS
> as
> a file access mechanism. Just read the MYSQL Newsgroup. BTW I'm about your
> age.
>
>
> "--CELKO--" <jcelko212@earthlink.net> wrote in message
> news:1133687403.902376.122910@g44g2000cwa.googlegroups.com...
>
>

Alexander Kuznetsov

2005-12-05, 1:24 pm

> In SQL Server 2005 we can do it in one very simple statement utilising FOR
> XML extensions, this makes for less code, less complexity and the logic is
> coded once in a central location - do you not agree that is good?

Tony,

how much is the output from FOR XML taxing the network bandwidth?
Most of the xml I'm dealing with is at least 50 times smaller when
zipped.

Tony Rogerson

2005-12-05, 1:24 pm

The beuty about this fellow, eg..

select type,
(
select name + ',' as [text()]
from sys.objects soi
where soi.type = t.type
order by name
for xml path( '' ), root( 'sysobjects' ), type
)
from ( select distinct type from sys.objects ) as t

Will give output like this...

D <sysobjects> DF__spt_value__statu
__436BFEE3,</sysobjects>
IT
<sysobjects> queue_messages_10031
50619,queue_messages
_1035150733,queue_me
ssages_1067150847,</sysobjects>
P <sysobjects> sp_MSrepl_startup,sp
_MScleanupmergepubli
sher,</sysobjects>
S
<sysobjects> sysrowsetcolumns,sys
rowsets,sysallocunit
s,sysfiles1,syshobtc
olumns,</sysobjects>
SQ
<sysobjects> QueryNotificationErr
orsQueue,EventNotifi
cationErrorsQueue,Se
rviceBrokerQueue,</sysobjects>
U
<sysobjects> spt_fallback_db,spt_
fallback_dev,spt_fal
lback_usg,spt_monito
r,spt_values,</sysobjects>

Which isn't XML, in fact take the root off and you are just left with the
concatenated data - no tags, its an extension to the FOR XML just for this
purpose which is requested a lot.

Taking the ROOT off gives...

D DF__spt_value__statu
__436BFEE3,
IT
queue_messages_10031
50619,queue_messages
_1035150733,queue_me
ssages_1067150847,
P sp_MScleanupmergepub
lisher,sp_MSrepl_sta
rtup,
S
sysallocunits,sysasy
mkeys,sysbinobjs,sys
binsubobjs,syscerts,
syschildinsts,syscls
objs,syscolpars,sysc
onvgroup,sysdbfiles,
sysdbreg,sysdercv,sy
sdesend,sysendpts,sy
sfiles1,sysftinds,sy
sguidrefs,syshobtcol
umns,syshobts,sysidx
stats,sysiscols,sysl
nklgns,syslogsh
ippers,sysmultiobjre
fs,sysnsobjs,sysobjk
eycrypts,sysobjvalue
s,sysowners,sysprivs
,sysqnames,sysremsvc
binds,sysrmtlgns,sys
rowsetcolumns,sysrow
setrefs,sysrowsets,s
ysrts,sysscalartypes
,sysschobjs,sysseref
s,syssingleobjrefs,s
yssqlguides,systyped
subobjs,sysuser
msgs,syswebmethods,s
ysxlgns,sysxmitqueue
,sysxmlcomponent,sys
xmlfacet,sysxmlplace
ment,sysxprops,sysxs
rvs,
SQ
EventNotificationErr
orsQueue,QueryNotifi
cationErrorsQueue,Se
rviceBrokerQueue,
U
MSreplication_option
s,seqnumbers,spt_fal
lback_db,spt_fallbac
k_dev,spt_fallback_u
sg,spt_monitor,spt_v
alues,

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"Alexander Kuznetsov" < AK_TIREDOFSPAM@hotma
il.COM> wrote in message
news:1133805541.815085.250880@g43g2000cwa.googlegroups.com...
>
> Tony,
>
> how much is the output from FOR XML taxing the network bandwidth?
> Most of the xml I'm dealing with is at least 50 times smaller when
> zipped.
>

Alexander Kuznetsov

2005-12-05, 1:24 pm

Tony,

that's impressive

Tony Rogerson

2005-12-05, 8:25 pm

Impressive - now that's an understatement - its blumin fantastic!

Can you imagine the amount of coding and complexity it replaces! And the
best thing about it is that it scales and performs really well too.

Itzik Ben-Gan showed me it and since I've played with it, its become one of
those you can use it everywhere solutions :)

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"Alexander Kuznetsov" < AK_TIREDOFSPAM@hotma
il.COM> wrote in message
news:1133810551.497956.115980@g44g2000cwa.googlegroups.com...
> Tony,
>
> that's impressive
>

bika

2005-12-05, 8:25 pm

Thanks for the great ideas everyone.

Tony,
I tried to implement your code but every time I get an error msg, it
doesn't accept elements after "FOR XML"

Tony Rogerson

2005-12-06, 3:23 am

Its SQL Server 2005 only - are you using 2005?

Tony

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"bika" <aesahab@gmail.com> wrote in message
news:1133827068.526744.68480@g14g2000cwa.googlegroups.com...
> Thanks for the great ideas everyone.
>
> Tony,
> I tried to implement your code but every time I get an error msg, it
> doesn't accept elements after "FOR XML"
>

Rob Smith

2005-12-20, 8:24 pm

Thank you for your response. I too needed to violate years of SQL
theory and flatten out a table into one row per person. Our need is to
easily include and exclude people for solicitation based on their role.
Your statement got reduced a half dozen views down to one and the
performance is better.

Thanks again!

*** Sent via Developersdex http://www.droptable.com ***

Sponsored Links