October 5, 2011 at 7:48 am
I have a table as below
(Table1)
State City
KA Bang
KA Mang
KA Srng
MH Dand
MH Than
DL Vijn
DL Agra
I need to show output as
State City
KA Bang,Man,Srng
MH Dand,Than
DL Vijn,Agra
Thanks in advance.
October 5, 2011 at 9:54 am
The standard way is something like the following:
SELECT DISTINCT [State]
, Stuff(
(SELECT ', ' + City
FROM Table1 AS t2
WHERE t1.[State] = t2.[State]
ORDER BY City
FOR XML PATH('')
)
, 1 -- start position
, 2 -- number of characters
, '' -- replacement string
) AS Cities
FROM Table1 AS t1
Of course, this has to scan Table1 twice, so if you have a table that already contains distinct states, it would be better to use that for the outer query.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply