Beyondrelational

Friday, May 28, 2010

Vertical to Horizontal

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


Create table testtable2
(
ID varchar(25) not null,
name varchar(25) null
)

insert testtable2 values ('A','2ZS')
insert testtable2 values ('A','RRT')
insert testtable2 values ('A','ABC')
insert testtable2 values ('B','22R')
insert testtable2 values ('B','123')
insert testtable2 values ('C','WWW')


select * from testtable2
SELECT t.id,LEFT(nl.namelist,LEN(nl.namelist)-1)  AS name
FROM (SELECT DISTINCT id FROM testtable2) t
CROSS APPLY (SELECT name + ',' AS [text()]
             FROM testtable2
             WHERE id=t.id
             FOR XML PATH(''))nl(namelist)

1 comment: