Search This Blog

Monday, 3 September 2012

Single comma separated row into multiple rows

1.Single comma separated row into multiple rows.
Lets create table
  Create table #temp (Country varchar(15), City varchar(50))
  Insert into #temp Values ('INDIA','Pune, New Delhi,Mumbai, Chandigarh')
  Insert into #temp Values ('USA','New York, New Jersey,Washington, California')
lets see the Out of this table below
#Output1:-
Country   City
INDIA     Pune, New Delhi,Mumbai, Chandigarh
USA        New York, New Jersey,Washington, California
Excecute this query
SELECT temp.Country,     
Split.temp.value('.', 'VARCHAR(20)') AS city
FROM  (SELECT Country,           CAST ('<M>' + REPLACE([City], ',', '</M><M>') + '</M>' AS XML) AS String FROM  #temp) AS temp  CROSS APPLY String.nodes ('/M') AS Split(temp);

Output2:-
Country    city
INDIA     Pune
INDIA     New Delhi
INDIA     Mumbai
INDIA     Chandigarh
USA         New York
USA         New Jersey
USA         Washington
USA          California
2.Multiple rows into a single comma separated row as #Output1

Now you put the output2 in another table  i.e #temp2
SELECT temp.Country,     
Split.temp.value('.', 'VARCHAR(20)') AS city
into #temp2
FROM  (SELECT Country, CAST ('<M>' + REPLACE([City], ',', '</M><M>') + '</M>' AS XML) AS String       FROM  #temp) AS temp  CROSS APPLY String.nodes ('/M') AS Split(temp);

Now Select This Query

select distinct  t1.Country, (
select t.City + ','
from #temp2 t
where t.Country=t1.Country
order by t.City
for xml path( '' )
)
from #temp2  t1