function to return values as comma-delimited

[krisplus5]

Is there an ansi sql function that will return a set of values, such that :

select id, score from tests
which normally returns something like:
id score
-- -----
1 A
1 B
2 A
2 D
3 C

would instead return:
id score
-- ------------
1 A,B
2 A,D
3 C

I can achieve this if there is a set number of scores, however, in this case there are an undefined number of scores for each id.

Thanks for any clues.
Kris

 

[tlbroadbent]

I'm not aware of a strictly ANSI function to concatenate values of a coulmn from all rows. I've written the following procedure. It works in SQL 2000. It can be modified to work in SQL 7 by replacing the table variable with a temp table.

Use MyDatabase
Go

Declare @id Varchar(30), @av Varchar(400), @Dlm Char
Set @Dlm = ','

Declare @Tbl Table
(ID Varchar(30),
AllValues Varchar(2000))

Declare ID_Cursor Cursor For
Select Distinct ID From MyTable

Open ID_Cursor

Fetch Next From ID_Cursor Into @id
While @@Fetch_Status=0
Begin
Set @av=Null
Select @av = Coalesce (@av + @Dlm, '') + Firstname
From MyTable Where ID=@id
Insert @Tbl Values (@id, @av)
Fetch Next From ID_Cursor Into @id
End

Close ID_Cursor
Deallocate ID_Cursor

Select * From @Tbl Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[RickCole]

Try the following and see if it's something like you want.

Rick.

create table test_data
(id_num int not null,
score char(1) not null)

insert into test_data values (1,'A')
insert into test_data values (1,'B')
insert into test_data values (2,'A')
insert into test_data values (2,'D')
insert into test_data values (3,'C')


create table #temp_test_data
(temp_id_num int not null,
temp_score char (1) not null,
temp_id_count int not null)

insert Into #temp_test_data (temp_id_num, temp_score, temp_id_count)

select b.id_num, b.score,
(select count(*) from test_data a
where a.id_num = b.id_num And a.score<=b.score)
from test_data b
order by b.id_num, b.score



declare @temp_id_count int, @sql nvarchar(4000), @loop int

select @temp_id_count=max(temp_id_count) from #temp_test_data

set @sql='Select temp_id_num'


set @loop=1
while @temp_id_count > 0
begin
set @sql=@sql + ', ' + char(10) +
' max(case temp_id_count when ' +
ltrim(str(@loop)) +
' then temp_score else '''' end) as Col' +
ltrim(str(@loop))
set @loop=@loop+1
set @temp_id_count=@temp_id_count-1
end


set @sql=@sql +
char(10) +
'from #temp_test_data group by temp_id_num' +
char(10)

print @sql

exec(@sql)

drop table #temp_test_data
drop table test_data

 

[krisplus5]

Yes, both of these work well. Thanks. Unfortunately, my client is looking for an ANSI sql solution (that will function in Access as well as SQL Server, etc.). The following works if I have a specific number of scores, and add a date field to the record.

select a.id, a.score+', '+b.score+', '+c.score+', '+d.score "Scores"
from tests a, tests b, tests c, tests d
where a.id = b.id
  and b.id = c.id
  and c.id = d.id
  and a.test_date = (select min(test_date) from tests where id = a.id)
  and b.test_date = (select min(test_date) from tests where id = b.id and
test_date > a.test_date)
  and c.test_date = (select min(test_date) from tests where id = c.id and
test_date > b.test_date)
  and d.test_date = (select min(test_date) from tests where id = d.id and
test_date > c.test_date)

Of course, this isn't really helpful for the true problem which is how to do it for an unknown and varying number of scores.

Thanks,
Kris