Is this possible ?

[ROWEL GARIL]

I don't know if this is possible but would appreciate any help.

RefDes table:
------------------------------------------
ID RefDes
------------------------------------------
123 VR1
123 C1
124 C2
123 C2
123 R1
125 R2
------------------------------------------

Is it possible to write a SELECT statement that will produce the
following?

-------------------------------------------
ID AllRefDes
-------------------------------------------
123 VR1, C1, C2, R1

As you see from the example above, all I need are the RefDes
concatenated together separated by a comma for a particluar ID.

Thanks.

[campkev]

I am at home and don't have sql server here so I can't test this to make sure it works 100% but you want something like this. Let me know if you still need help and I will check it when I get to work tomorrow.

declare @id int
declare @item varchar(255)
declare @itemlist varchar(255)

set @id = 123
set @item = (select max(RefDes) from RefDes where id = @id order by RefDes Desc)
set @itemlist =  Cast(@id as varchar) + ' ' + @item

while @item is not null
begin 
	set @item = (select max(RefDes) from RefDes where id = @id and  RefDes < @item order by RefDes Desc)
	if (@item is not null)
	set @itemlist = @itemlist + ", " + @item
end


select @itemlist

[ROWEL GARIL]

Hi campkev,

Thank you for the SQL script. I removed the ORDER BY from the sub-queries since it's not acceptable. Other than that the script worked fine.

Thanks,