| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
|
|
Juls
Yak Posting Veteran
USA
51 Posts |
 Posted - 06/05/2002 : 11:35:31
|
quote:
Joern asks How do I aggregate data from multiple rows into a delimited list?<P>Article <a href="/item.asp?ItemID=256">Link</a>.
Hi, I tried using the code in the article do just what the article asks, however i can not get it exactly. I get all my values in one row separated by commas. However, I can't separate the values out with the identifier. Which ones go with which number.. 1, 2, 3
Please help
|
 |
|
|
robvolk
Most Valuable Yak
USA
15557 Posts |
Posted - 06/05/2002 : 11:47:35
|
Uhhhhhhhhhhhhhhhh, what's the problem exactly? Can you provide some sample data, and the output you want?
|
|
|
|
Juls
Yak Posting Veteran
USA
51 Posts |
Posted - 06/05/2002 : 12:29:18
|
quote:
Uhhhhhhhhhhhhhhhh, what's the problem exactly? Can you provide some sample data, and the output you want?
Here it is:
I have a table that has values structured as follows:
PersonID Degree
55 MD
55 Phd
55 RN
60 MD
60 Phd
I need a stored procedure that will give me output like this:
PersonID Degree
55 MD, Phd, RN
60 MD, Phd
With the code above, as well as with the article code I only get the list of degrees in one line separated by commas, even though I pull out the Person ID it doesn't group by this ID
Thanks,
juls
|
|
|
|
robvolk
Most Valuable Yak
USA
15557 Posts |
Posted - 06/05/2002 : 12:44:48
|
Take a look here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=15651
There are several other links within that one, you may have to drill down a bit, but there are at least 3 methods for doing this. Before you ask, it CANNOT be done with a single SELECT statement, but that's not an issue really.
|
|
|
|
Page47
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 06/05/2002 : 12:47:29
|
Juls, I don't know why you can't get it to work . . .
This should...
/*
--This is the part you should always post so that ppl who
--come along to help you for free don't have to waste their
--time typing it in
create table Juls (
PersonID int,
Degree varchar(3) )
insert juls
select 55,'md'
union select 55,'phd'
union select 55,'rn'
union select 60,'md'
union select 60,'phd'
*/
declare @List varchar(100), @LastID int
select @List = '', @LastID = ''
select
PersonID,
Degree,
convert(varchar(100),NULL) as list
into
#rowset
from
Juls
order by
PersonID,
Degree
update
#rowset
set
@List = list = case
when @LastID <> PersonID then Degree
else @List + ',' + Degree
end,
@LastID = PersonID
select
PersonID,
max(list)
from
#rowset
group by
PersonID
go
drop table #rowset
<O> |
|
|
|
julesr
Starting Member
United Kingdom
13 Posts |
Posted - 06/20/2002 : 07:41:49
|
[quote]
Juls, I don't know why you can't get it to work . . .
This should...
[code]
Thanks for posting this solution, it works great for me. I'd now like to take it one step further. My situation is virtually identical aside from the fact that Degree is an ntext field. I'd appreciate any insight you might have
Jules
http://www.charon.co.uk |
|
|
|
archngl
Starting Member
USA
1 Posts |
Posted - 09/10/2002 : 12:16:43
|
RocketScientist's solution can be achieved without the need for a cursor or a temp table with some fairly simple code with I think has been discussed here before.
Create table #Foo(col1 int, col2 varchar(10))
insert into #foo
Select 1, 'a'
union Select 2, 'b'
union Select 2, 'c'
union Select 2, 'd'
union Select 3, 'a'
Declare @string varchar(8000)
Set @String = ''
--Build the string list
Select @string = @string + cast(col2 as varchar) + ',' from #Foo where col1 = 2
--Trim the trailing comma
Select @String = left(@string,len(@string)-1)
--Here is your list for a single col1 value.
print @string
Now to solve your specific solution you can do 1 of 2 ways. with an update statement(see Page47 solution) to the output table or a cursor to cycle through the input statements. I'll use the cursor method below.
/*
Create table #Foo(col1 int, col2 varchar(10))
insert into #foo
Select 1, 'a'
union Select 2, 'b'
union Select 2, 'c'
union Select 2, 'd'
union Select 3, 'a'
Create table #Foo2(col1 int, col2 varchar(10))
*/
truncate table #foo2
Declare @col1 int
Declare @string varchar(8000)
declare Col1 insensitive scroll cursor
for select Distinct col1 from #foo
for read only
Open Col1
fetch next from Col1 into @col1
While @@Fetch_Status = 0
Begin
Set @String = ''
--Build the string list
Select @string = @string + cast(col2 as varchar) + ',' from #Foo where col1 = @col1
--Trim the trailing comma
Select @String = left(@string,len(@string)-1)
--Here is your list
Insert into #foo2 Select @Col1, @String
fetch next from Col1 into @col1
End
close Col1
Deallocate Col1
Select * from #foo2
Edited by - archngl on 09/10/2002 12:17:29 |
|
|
|
sheehek
Starting Member
1 Posts |
Posted - 10/11/2002 : 15:23:22
|
Here's how I would do it (may already be out there in another thread):
Step 1: table called tester and put in the data.
Step 2: create a user-defined function that uses COALESCE.
Step 3: create a single select query
The results:
1, a
2, b, c, d
3, a
4
Note the results even account for the scenerio where you just want the id and there are no letters/suffixes after it.
K.
------------------------------------------
--Step 1: table called tester and put in the data.
create table tester (MyId int null, MyLetter char(1) null)
insert into tester
select
1, 'a'
insert into tester
select
2 ,'b'
insert into tester
select
2 ,'c'
insert into tester
select
2 ,'d'
insert into tester
select
3, 'a'
insert into tester
select
4, null
--Step 2: create a user-defined function that uses COALESCE.
create function CreateCSVString(@nMyID int)
returns varchar(1000)
AS
BEGIN
DECLARE @sMyString varchar(1000)
select @sMyString = COALESCE(@sMyString + ', ', '') + MyLetter from tester
where myid = @nMyId
RETURN @sMyString
END
--Step 3: create a single select query
select cast(myID as varchar) + ISNULL(NULLIF(', ' + ISNULL(dbo.CreateCSVString(myID),''), ', '),'')
FROM tester t1
group by myID
|
|
|
| |
Topic |
|
|
|
Article: Converting Multiple Rows into a CSV String
