How to stay young and energetic. Idol rules of life.

This is good example.

CONVERT COLUMN VALUES TO COMMA SEPARATED ONE ROW VALUE

I have a query the returns country....state...and cities 

the cities are creating ore rows than required, I need to re-write my query so that the cities column instead come in single row separated by commas...
here is the data for more understanding

 

CREATE TABLE [dbo].[test12](
   [title] [varchar](51) NULL,
   [subtitle] [varchar](52) NULL,
   [value] [varchar](53) NULL
) 

INSERT INTO test12 (  title,subtitle,value) VALUES (  'USA', 'PA', 'PHILLY')
INSERT INTO test12 (  title,subtitle,value) VALUES (  'USA', 'PA', 'PITTSBURG')
INSERT INTO test12 (  title,subtitle,value) VALUES (  'USA', 'PA', 'WARREN')
INSERT INTO test12 (  title,subtitle,value) VALUES (  'USA', 'PA', 'UNION')
INSERT INTO test12 (  title,subtitle,value) VALUES (  'USA', 'NJ', 'EDISON')
INSERT INTO test12 (  title,subtitle,value) VALUES (  'USA', 'NJ', 'PARSIPPANY')
INSERT INTO test12 (  title,subtitle,value) VALUES (  'USA', 'NJ', 'METROPARK')
INSERT INTO test12 (  title,subtitle,value) VALUES (  'USA', 'NJ', 'PRINCTON')
INSERT INTO test12 (  title,subtitle,value) VALUES (  'USA', 'NJ', 'HAMILTON')
INSERT INTO test12 (  title,subtitle,value) VALUES (  'USA', 'NJ', 'ISELIN')
INSERT INTO test12 (  title,subtitle,value) VALUES (  'USA', 'NJ', 'PATERSON')
INSERT INTO test12 (  title,subtitle,value) VALUES (  'USA', 'NJ', 'PARAMUS')
INSERT INTO test12 (  title,subtitle,value) VALUES (  'USA', 'VA', 'mClEAN')
INSERT INTO test12 (  title,subtitle,value) VALUES (  'USA', 'VA', 'FAIRFAX')
INSERT INTO test12 (  title,subtitle,value) VALUES (  'USA', 'VA', 'ARLINGTON')
INSERT INTO test12 (  title,subtitle,value) VALUES (  'USA', 'VA', 'STAUNTON')
INSERT INTO test12 (  title,subtitle,value) VALUES (  'USA', 'VA', 'NEWCITY')
INSERT INTO test12 (  title,subtitle,value) VALUES (  'USA', 'VA', 'OLDCITY')
INSERT INTO test12 (  title,subtitle,value) VALUES (  'USA', 'FL', 'MIAMI')
INSERT INTO test12 (  title,subtitle,value) VALUES (  'USA', 'FL', 'TAMPA')
INSERT INTO test12 (  title,subtitle,value) VALUES (  'USA', 'FL', 'ORLANDO')
INSERT INTO test12 (  title,subtitle,value) VALUES (  'USA', 'FL', 'JACKSONVILLE')
INSERT INTO test12 (  title,subtitle,value) VALUES (  'USA', 'FL', 'NEW')
INSERT INTO test12 (  title,subtitle,value) VALUES (  'USA', 'FL', 'OLD')


 select * from  test12
   

required output 

   USA |  PA  | PHILLY,PITTSBURG,WARREN,UNION
   USA |  NJ  |  EDISON,PARSIPPANY ,METROPARK , PRINCTON....
   USA |  VA  |  mClEAN, FAIRFAX, ARLINGTON, STAUNTON...
   AND SO ON.............

The Solution

;with SampleDataR as
(
select *, ROW_NUMBER() over (partition by title, subtitle order by value) rownum
from test12
) 
select distinct title, subtitle,(
select value 
   + case when s1.rownum = (select MAX(rownum) from SampleDataR where title = s1.title and subtitle = s1.subtitle)
   then '' else ',' end from SampleDataR s1
where s1.title = s2.title and s1.subtitle = s2.subtitle
for xml path(''),type).value('(.)[1]','varchar(max)') csvList
from SampleDataR s2

I refer this link :- https://www.sqlservercentral.com/Forums/Topic1260952-338-1.aspx