Pages - Menu

Thursday, March 3, 2016

Convert Column values to Comma separated one row value

It is very common task to create comma separated values (CSV) from table column. While doing some development, we often need to do this. I will explain this with a very easy example.

Example:

 Assume we are having a table with the following structure and data.

how to Convert Column values to Comma separated one row value

Syntax: 

To Create the above mentioned table and insert data into it.

CREATE TABLE EMPLOYEE_DETAILS (COUNTRY VARCHAR(20), CITY VARCHAR(20), NAME VARCHAR(20))
INSERT INTO EMPLOYEE_DETAILS VALUES ('INDIA','DELHI','AMAN')
INSERT INTO EMPLOYEE_DETAILS VALUES ('INDIA','DELHI','AMAR')
INSERT INTO EMPLOYEE_DETAILS VALUES ('INDIA','DELHI','AKASH')
INSERT INTO EMPLOYEE_DETAILS VALUES ('INDIA','DELHI','ARAVIND')
INSERT INTO EMPLOYEE_DETAILS VALUES ('INDIA','CHENNAI','MAYUR')
INSERT INTO EMPLOYEE_DETAILS VALUES ('INDIA','CHENNAI','MUKESH')
INSERT INTO EMPLOYEE_DETAILS VALUES ('INDIA','CHENNAI','MAYANK')
INSERT INTO EMPLOYEE_DETAILS VALUES ('INDIA','AGRA','RAVI')
INSERT INTO EMPLOYEE_DETAILS VALUES ('INDIA','AGRA','RINKU')


Required Output:

Convert Column values to Comma separated one row value

In the required output we want to have all the names in a single row with comma separated having same country and city.

Syntax :

 To create Comma Separated values in single row.

SELECT DISTINCT
COUNTRY,
CITY,          
SUBSTRING(  
(  
SELECT ','+ED1.NAME  AS [text()]  
FROM EMPLOYEE_DETAILS ED1  
WHERE ED1.COUNTRY = ED2.COUNTRY  AND ED1.CITY = ED2.CITY 
FOR XML PATH ('')  
), 2, 1000
) [NAME]  
FROM EMPLOYEE_DETAILS ED2

Note: 

Text keyword having in red color as background color should be in lower case.

No comments:

Post a Comment