Thursday, January 29, 2015

MSSQL - Get Comma Separated String from multiple rows

I'm a big fan of Select2 library and there are instances where I have to use the multiple selections. like the ones below.
Select2 Tagging Support
I store them values in a table structure which looks like the following.
1:M relationship
This table maps the One-to-Many relationship between my service profiles and facilities. (One profile has many facilities). Using Select2, I can select many facilities at once.

Select2 serializes the Facility IDs as a comma separated string and I have to split it into individual facility IDs to store in the database. Storing the CSV string is a bad idea. How the hell are you going to query them?

-- delcare a new test table
DECLARE @Table1 TABLE (
                  ID    INT,
                  Value INT);

-- insert dummy values
INSERT  INTO @Table1
VALUES (1, 100),
       (1, 200),
       (1, 300),
       (1, 400);

Query To convert the values into a CSV string

SELECT   ID,
         STUFF((SELECT ', ' + CAST (Value AS VARCHAR (10)) AS [text()]
                FROM   @Table1
                WHERE  ID = t.ID
                FOR    XML PATH (''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ' ') AS List_Output
FROM     @Table1 AS t
GROUP BY ID;

Result

╔════╦═════════════════════╗
║ ID ║     List_Output     ║
╠════╬═════════════════════╣
║  1 ║  100, 200, 300, 400 ║
╚════╩═════════════════════╝