Hi team,
i have following data and i need grouped output id wise.
declare @test table (id varchar(10) , type varchar(100))
insert into @test
values ('a1001' , 'access') , ('a1001' , 'success'), ('a1001' , 'retain') ,
('b2002' , 'reward') ,('b2002' , 'success') , ('b2002' , 'super')
following is the output that is required. please suggest the approach.
id type
a1001 access , success , retain
b2002 reward , success , super
i have done some work myself but not able to add id field as required.
select
stuff (
(select ',' + [type]
from @test
for xml path ('')),1,1,'') csv
Because it all comes from one table there is a need to only get distinct values from @test table. I suppose you could use DISTINCT but I think that may be inefficient (getting all the DUPs and THEN Sorting and De-duping them), so I've come up with this conveluted method which just gets distinct IDs from @test table and then users APPLY to make the CSV list
SELECT id
, csv
FROM
(
SELECT DISTINCT id
FROM @test
) AS P
OUTER APPLY
(
SELECT STUFF(
(
SELECT ',' + type
FROM @test AS C
WHERE C.id = P.id
ORDER BY C.id
FOR XML PATH(''), TYPE
).value('.', 'varchar(max)')
, 1, 1, '') AS CSV
) AS X
If what you actually have is a Parent Table, with the IDs, and a Child Table with the CSV [type] values, then this would be easier to understand I think:
declare @parent table (id varchar(10) , name varchar(10))
insert into @parent
values ('a1001' , 'Parent 1') , ('b2002' , 'Parent 2')
SELECT id
, name
, STUFF(
(
SELECT ',' + type
FROM @test AS C
WHERE C.id = P.id
ORDER BY C.id
FOR XML PATH(''), TYPE
).value('.', 'varchar(max)')
, 1, 1, '') AS CSV
FROM @parent AS P