Can anyone help me out in converting below tables data to a defined format as below:
Table Data:
id country city
101 India delhi
101 india up
101 India hp
102 US NY
102 US RG
103 China AN
104 Japan PP
Output
id country city
101 India delhi;up;hp
102 US NY;RG
103 China AN
104 Japan PP
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
gives
ID country cities
101 India delhi; hp; up
102 US NY; RG
103 China AN
104 Japan PP
Given you can't necessarily guarantee the quality of the data, it might be worth tweaking the end of the XML PATH line as follows:
FOR XML PATH (''), type).value('.','varchar(max)'),1,2,'') as cities
You might like to look at Anith Sen's comprehensive answer to this question on Simple-Talk at Concatenating Row Values in Transact-SQL
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.
