July 24, 2015 at 10:01 am
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 seperated based on City,state, zip. Any sample query please
July 24, 2015 at 11:24 am
KGJ-Dev (7/24/2015)
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 seperated based on City,state, zip. Any sample query please
I'm not sure I can see a practical application for the expected output to contain 1001 for both of the zipcode values, so I coded around that, as follows:
DECLARE @table AS TABLE (
[Name] varchar(200),
Zipcode varchar(10),
[State] varchar(10),
City varchar(50)
);
INSERT INTO @table (Zipcode, [Name], [State], City)
SELECT 1000 AS zip, 'Test1' AS [Name],'NY' AS [State],'New york' AS City UNION ALL
SELECT 1000 AS zip, 'Test2' AS [Name],'NY' AS [State],'New york' AS City UNION ALL
SELECT 1000 AS zip, 'Test3' AS [Name],'NY' AS [State],'New york' AS City UNION ALL
SELECT 1000 AS zip, 'Test4' AS [Name],'NY' AS [State],'New york' AS City UNION ALL
SELECT 1001 AS zip, 'Test5' AS [Name],'NY' AS [State],'Buffalo' AS City UNION ALL
SELECT 1001 AS zip, 'Test6' AS [Name],'NY' AS [State],'Buffalo' AS City UNION ALL
SELECT 1001 AS zip, 'Test7' AS [Name],'NY' AS [State],'Buffalo' AS City UNION ALL
SELECT 1001 AS zip, 'Test8' AS [Name],'NY' AS [State],'Buffalo' AS City;
DECLARE @ExpectedOutput AS TABLE (
Zipcode varchar(10),
[State] varchar(10),
City varchar(50),
[Name] varchar(max)
);
INSERT INTO @ExpectedOutput (Zipcode, [Name], [State], City)
SELECT 1001 AS zip, 'Test1,Test2,Test3,Test4' AS [Name],'NY' AS [State],'New York' AS City UNION ALL
SELECT 1001 AS zip, 'Test5,Test6,Test7,Test8' AS [Name],'NY' AS [State],'Buffalo' AS City;
SELECT * FROM @table
SELECT * FROM @ExpectedOutput
SELECT T.Zipcode, T.[State], T.City,
STUFF(
(
SELECT ', ' + [Name]
FROM @table AS T2
WHERE T2.[State] = T.[State]
AND T2.City = T.City
AND T2.Zipcode = T.Zipcode
FOR XML PATH('')
), 1, 2, '') AS [Name]
FROM @table AS T
GROUP BY T.Zipcode, T.[State], T.City
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 24, 2015 at 4:12 pm
thanks Steve and to avoid & charecter issue, slightly modified your query. Thanks for your base query
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]);
July 24, 2015 at 8:12 pm
You're welcome. It's always good to see how the query you provide gets used, and even better when you get a good idea on why the original poster needed to make changes. You never know what you might learn in the process.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply