CODEWITHSUNDEEP
sqlsql-server
Jenny Tran
Jenny Tran

Reputation: 251

Get unique values in column separated by comma

I have a classic ASP online form to submit and insert data into this table. This form will insert multiple rows into a table. The form will gather some checkboxes and insert True for checked and False for unchecked on Status column.

I tried to create a query to STUFF Menu_id that has True Status, separate by a comma.

For example:

I tried a query below, but it shows error:

Conversion failed when converting the varchar value ', ' to data type int

ID   Refnumber   Menu_id   Status
------------------------------------
01   12121313    1          True
02   12121313    2          False
03   12121313    3          False
04   12121313    4          True

05   28273882    1          True
06   28273882    2          True
07   28273882    3          True
08   28273882    4          False

This is my query :

SELECT  ID, Refnumber, Status
Menu_id = 
    STUFF((SELECT ', ' + Menu_id
           FROM Report b 
           WHERE b.Refnumber = a.Refnumber 
          FOR XML PATH('')), 1, 2, '')
FROM Report a
GROUP BY  ID, Refnumber,Menu_id,Status

Can you please help me?

Upvotes: 2

Views: 1663

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Use CONCAT() :

SELECT ID, Refnumber, Status
       Menu_id = STUFF((SELECT CONCAT(', ',b.Menu_id)
                        FROM Report b 
                        WHERE b.Refnumber = a.Refnumber 
                        FOR XML PATH('')
                       ), 1, 2, ''
                      ) 
FROM Report a
GROUP BY ID, Refnumber, Status;

CONCAT() is available SQL Server 12 or +, you can also do conversation :

SELECT ', ' + CONVERT(VARCHAR(255), b.Menu_id)

Upvotes: 1

Nguyễn Văn Phong
Nguyễn Văn Phong

Reputation: 14198

You can achieve it by this simple way, live demo here

SELECT DISTINCT C2.Refnumber, 
SUBSTRING(
(
   SELECT ', ' + CAST(C1.Menu_id AS VARCHAR(20))
   FROM TempTable C1
   WHERE C1.Refnumber = C2.Refnumber AND C1.Status = 'True'
  FOR XML PATH ('')
), 2, 1000) AS "MenuIdList"
FROM TempTable C2

Output

Refnumber   MenuIdList
12121313     1, 4
28273882     1, 2, 3

Upvotes: 2

Related Questions