Getting data from multiple rows into one column

[bika]

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]

bika (ae*****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[gjvdkamp]

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]

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" <ae*****@gmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.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--]

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]

--CELKO-- (jc*******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Alexander Kuznetsov]

> 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]

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--" <jc*******@earthlink.net> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.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]

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

Rich
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

--CELKO-- (jc*******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Tony Rogerson]

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" <ry****@sbcglobal.net> wrote in message
news:OI*****************@newssvr19.news.prodigy.co m...

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--" <jc*******@earthlink.net> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.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.

[Alexander Kuznetsov]

> 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]

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_1003150619,queue_messag es_1035150733,queue_messages_1067150847,</sysobjects>
P <sysobjects>sp_MSrepl_startup,sp_MScleanupmergepub lisher,</sysobjects>
S
<sysobjects>sysrowsetcolumns,sysrowsets,sysallocun its,sysfiles1,syshobtcolumns,</sysobjects>
SQ
<sysobjects>QueryNotificationErrorsQueue,EventNoti ficationErrorsQueue,ServiceBrokerQueue,</sysobjects>
U
<sysobjects>spt_fallback_db,spt_fallback_dev,spt_f allback_usg,spt_monitor,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_1003150619,queue_messages_103515073 3,queue_messages_1067150847,
P sp_MScleanupmergepublisher,sp_MSrepl_startup,
S
sysallocunits,sysasymkeys,sysbinobjs,sysbinsubobjs ,syscerts,syschildinsts,sysclsobjs,syscolpars,sysc onvgroup,sysdbfiles,sysdbreg,sysdercv,sysdesend,sy sendpts,sysfiles1,sysftinds,sysguidrefs,syshobtcol umns,syshobts,sysidxstats,sysiscols,syslnklgns,sys logshippers,sysmultiobjrefs,sysnsobjs,sysobjkeycry pts,sysobjvalues,sysowners,sysprivs,sysqnames,sysr emsvcbinds,sysrmtlgns,sysrowsetcolumns,sysrowsetre fs,sysrowsets,sysrts,sysscalartypes,sysschobjs,sys serefs,syssingleobjrefs,syssqlguides,systypedsubob js,sysusermsgs,syswebmethods,sysxlgns,sysxmitqueue ,sysxmlcomponent,sysxmlfacet,sysxmlplacement,sysxp rops,sysxsrvs,
SQ
EventNotificationErrorsQueue,QueryNotificationErro rsQueue,ServiceBrokerQueue,
U
MSreplication_options,seqnumbers,spt_fallback_db,s pt_fallback_dev,spt_fallback_usg,spt_monitor,spt_v alues,

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Alexander Kuznetsov" <AK************@hotmail.COM> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

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.

[Alexander Kuznetsov]

Tony,

that's impressive

[Tony Rogerson]

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************@hotmail.COM> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

Tony,

that's impressive

[bika]

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]

Its SQL Server 2005 only - are you using 2005?

Tony

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"bika" <ae*****@gmail.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.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]

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.developersdex.com ***