Firstly as per your Database it is storing comma separated value. So it is bit difficult to manage data. Instead of storing data in comma separated you can store multiple records for coma separated.
Secondly as you already developed and have existing data, please find solutions below:
Create a custom function using below code:
CREATE FUNCTION [dbo].[fnSplitString]
(
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @output TABLE(splitdata NVARCHAR(MAX)
)
BEGIN
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1
INSERT INTO @output (splitdata)
VALUES(SUBSTRING(@string, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END
RETURN
END
Write below query to achieve the result:
SELECT S.Rollno, STUFF((SELECT distinct ', ' + t1.City
from (SELECT CL.City
FROM CityList CL INNER JOIN
(SELECT splitdata from dbo.fnSplitString(S.ExamsAppearedinCityIDList,',')) AS Temp
ON CL.ID = Temp.splitdata) t1
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,2,'') Cities
FROM Student S