Getting data from multiple rows into one column

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

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

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

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

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.

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

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

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

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

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