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
                  ID    INT,
                  Value INT);

-- insert dummy values
VALUES (1, 100),
       (1, 200),
       (1, 300),
       (1, 400);

Query To convert the values into a CSV string

         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


║ ID ║     List_Output     ║
║  1 ║  100, 200, 300, 400 ║

Popular posts from this blog

Print a receipt using a Thermal Printer with C#.NET

Automatic redirect upon session timeout using ASP.NET MVC and Javascript

Complex Master-Detail Form using Knockout.js and ASP.NET MVC