Help Needed in Combining the result

Transact-SQL
1

Hi, please find the below sample data and expected result


Declare @table table(Name varchar(200),Zipcode varchar(10),State varchar(10),City varchar(50))

Declare @ExpectedOutput table(Zipcode varchar(10),State varchar(10),City varchar(50),Name varchar(max))

insert into @table(Zipcode,Name,State,City) 


select 1000  as zip, 'Test1' as store,'NY' as state,'New york' as City union all 

select 1000  as zip, 'Test2' as store,'NY' as state,'New york' as City union all 

select 1000  as zip, 'Test3' as store,'NY' as state,'New york' as City union all 

select 1000  as zip, 'Test4' as store,'NY' as state,'New york' as City  union all
select 1001  as zip, 'Test5' as store,'NY' as state,'Buffalo' as City union all 

select 1001  as zip, 'Test6' as store,'NY' as state,'Buffalo' as City union all 

select 1001  as zip, 'Test7' as store,'NY' as state,'Buffalo' as City union all 

select 1001  as zip, 'Test8' as store,'NY' as state,'Buffalo' as City 



insert into @ExpectedOutput(Zipcode,Name,State,City)

select 1001  as zip, 'Test1,Test2,Test3,Test4' as store,'NY' as state,'New York' as City union all
select 1001  as zip, 'Test5,Test6,Test7,Test8' as store,'NY' as state,'Buffalo' as City 


select * from @table


select * from @ExpectedOutput

basically i need to combine the name as comma separated based on City,state, zip. Any sample query please

2 
SELECT
	t1.zipcode,
	t1.State,
	t1.City,
	STUFF(t2.[Name],1,1,'') AS [Name]
FROM
	(SELECT DISTINCT zipCode, [State], City FROM @table) t1
	CROSS APPLY
	(
		SELECT ',' + [Name]
		FROM @table t2
		WHERE t2.Zipcode = t1.zipcode
			AND t2.State = t1.State
			AND t2.City = t2.City
		ORDER BY [Name]
		FOR XML PATH('')
	) t2 ([Name]);
3

Thanks James. one issue that if the name has & character the sql appends amp;

how to avoid this

4

Try this?

SELECT
	t1.zipcode,
	t1.State,
	t1.City,
	STUFF(t2.[Name],1,1,'') AS [Name]
FROM
	(SELECT DISTINCT zipCode, [State], City FROM @table) t1
	CROSS APPLY
	(
		SELECT
			(
				SELECT ',' + [Name]
				FROM @table t2
				WHERE t2.Zipcode = t1.zipcode
					AND t2.State = t1.State
					AND t2.City = t2.City
				ORDER BY [Name]
				FOR XML PATH(''), TYPE
			).value('.[1]','varchar(1024)')
	) t2 ([Name]);

Edit: You might want to change the length in the "varchar(1024)" to make it larger than the largest concatenated string you expect in the final results.

1 Like
5

Thanks James. Worked like charm