Transpose Single delimited rows into multiple rows

 

DECLARE @data NVARCHAR(MAX)  
 select  @data= 'Ahamedabad,Bengaluru,Chennai,Delhi'
 DECLARE @sql_xml XML = Cast('<root><U>'+ Replace(@data, ',', '</U><U>')+ '</U></root>' AS XML)
SELECT f.x.value('.', 'VARCHAR(max)') AS Transpose_Values
--INTO #Temp
FROM @sql_xml.nodes('/root/U') f(x)