Can anyone help me out in converting below tables data to a defined format as below:
id country city 101 India delhi 101 india up 101 India hp 102 US NY 102 US RG 103 China AN 104 Japan PP
id country city 101 India delhi;up;hp 102 US NY;RG 103 China AN 104 Japan PP
Answer by Kev Riley ·
You didn't state a SQL version, but here's one that works in 2005 onwards
declare @mytable table ( id int, country varchar(10), city varchar(10)) insert into @mytable select 101,'India','delhi' union select 101,'india','up' union select 101,'India','hp' union select 102,'US','NY' union select 102,'US','RG' union select 103,'China','AN' union select 104,'Japan','PP' SELECT distinct ID, country, stuff ( ( SELECT '; ' +city FROM @mytable t1 where t1.ID = t2.ID FOR XML PATH ( '' ) ) , 1 , 2 , '' ) as cities from @mytable t2
ID country cities 101 India delhi; hp; up 102 US NY; RG 103 China AN 104 Japan PP
Answer by TimothyAWiseman ·
Kev's answer is excellent and Phil's reference provides you many options. If you want to consider those options in context of their effeciency, you may also want to read https://www.sqlservercentral.com/articles/performance-tuning-concatenation-functions-and-some-tuning-myths-1 by Jeff Moden. It lays out several options, but also discusses why and how efficiently they work.
Of course, in the end Jeff recommends something very similar Kev's answer as being most efficient most of the time, but it is well worth reading if you want to actually understand the options.