Asp.net blogs

Friday 7 March 2014

Query to generate comma-separated list from one individual column data from database table in sql server using COALESCE()

Table Name > tbmap
                    Fields >  Place
                                    Data > Amritsar
                                           Bhutan
                                           Kerala
                                          

Query to generate comma-separated list from one individual column data
from database table in sql server using COALESCE()


Query >

 DECLARE @List VARCHAR(8000)

SELECT @List = COALESCE(@List + ',', '') + CAST(Place AS VARCHAR)
FROM   tbmap
where Place='PLACENAME'
SELECT @List as Place

Output :
Amritsar,Bhutan,Kerala

Query to generate comma-separated list from one individual column data from database table in sql server

Table Name > EmployeeDetails
           Fields >  EmployeeName
             Data > Rohin
                          Rajat
                          Rahul
                          Ishan

Query to generate comma-separated list from one individual column data from database table in sql server

Query >
SELECT STUFF((SELECT ',' + [EmployeeName]
              FROM @EmployeeDetails
              ORDER BY [EmployeeName]
              FOR XML PATH('')), 1, 1, '') AS [EmployeeName]

Output
Ishan,Rahul,Rajat,Rohin