Group data

1

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

2

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

3

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