quote:

---

Originally posted by scabral7

sql 2000:

Have this in Table:
A 2ZS
A RRT
A ABC
B 22R
B 123
C WWW

Need this in query result:
A 2ZS, RRT, ABC
B 22R, 123
C WWW

any ideas?

thanks
scott

---

declare @t1 table(c1 char(1), c2 char(5))
insert into @t1
select 'A','2ZS'
UNION ALL
SELECT 'A','RRT'
UNION ALL
SELECT 'A','ABC'
UNION ALL
SELECT 'B','22R'
UNION ALL
SELECT 'B','123'
UNION ALL
SELECT 'C','WWW'

declare @c1 char(1)
declare @liststr varchar(8000)
declare crs cursor
for select distinct c1 from @t1

select @c1 as c1, @liststr as c2 into #t1 where 1=2

open crs
fetch next from crs into @c1

while @@fetch_status = 0
begin
select @liststr=coalesce(@liststr+',', '')+c2 from @t1 where c1 = @c1
insert into #t1 values(@c1, @liststr)
set @liststr = null
fetch next from crs into @c1
end
select * from #t1
drop table #t1

close crs
deallocate crs

--test table
create  table 
test1
(c1 char(1), c2 varchar(5))
insert into test1
select 'A','2ZS'
UNION ALL
SELECT 'A','RRT'
UNION ALL
SELECT 'A','ABC'
UNION ALL
SELECT 'B','22R'
UNION ALL
SELECT 'B','123'
UNION ALL
SELECT 'C','WWW'


--udf to concat values
CREATE FUNCTION ConcatValues
(@c1 char(1)
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @ret varchar(8000)

SELECT @ret=COALESCE(@ret+',','') + c2
FROM test1
WHERE c1=@c1

RETURN @ret
END


--the solution using call to above udf
SELECT DISTINCT c1,dbo.ConcatValues(c1) AS ValList
FROM test1


--remove table created after test
drop table test1


output
-------------------------------
c1	ValList
A	2ZS,RRT,ABC
B	22R,123
C	WWW