Turn Sql Table into deliminated list

Question

If given a table like this

Group   Value
Item 1  A
Item 1  B
Item 1  C
Item 1  D
Item 2  Z
Item 2  Y
Item 2  X
Item 2  W

I would like a result in a table like this

Group   
Item 1  A|B|C|D
Item 2  Z|Y|X|W

Now I know how to do an individual group.

 SELECT @Item1= ISNULL(@Item1+ '|', '') + CodeValue
 FROM Codes
 WHERE CodeType = 'Item 1'

I guess I can put that in a loop. My question is there a more elegant way?

You should try to search for an answer on Google. You will find many answers. e.g. http://blog.namwarrizvi.com/?p=140 — Shantanu Gupta, Mar 24 '15 at 22:13
possible duplicate of [Optimal way to concatenate/aggregate strings](http://stackoverflow.com/questions/13639262/optimal-way-to-concatenate-aggregate-strings) — jpw, Mar 24 '15 at 22:13
There are quite a few duplicate questions on this problem for different versions of SQL Server; the technique using `for xml path` is probably the most common. — jpw, Mar 24 '15 at 22:14
http://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation — Aaron Bertrand, Mar 24 '15 at 22:38

score 0 · Answer 1 · edited Mar 25 '15 at 08:31

Find the bellow, Hope will satisfy

DECLARE @tABLE TABLE(ItemName varchar(10),Code char(1))
insert into @tABLE
select 'Item 1',  'A'
union
select 'Item 1',  'B'
union 
select 'Item 1',  'C'
union
select 'Item 1',  'D'
union
select 'Item 2',  'W'
union
select 'Item 2',  'X'
union
select 'Item 2',  'Y'
union
select 'Item 2',  'Z'

select distinct ItemName ,
STUFF((Select ' | '+Code
from @tABLE T1
where T1.ItemName=T2.ItemName
FOR XML PATH('')),1,2,'') from @tABLE T2

ItemName (No column name) Item 1 A | B | C | D Item 2 W | X | Y | Z

Turn Sql Table into deliminated list

1 Answers1