SQL Server query with multiple values in one column relating to another column

Question

Situation: This table holds the relation information between a Documents table and an Users table. Certain Users need to review or approve documents (Type). I would like to have it to where I could get all of the reviewers on one line if needed. So if three users review Document 1, then a row would have 346, 394, 519 as the value, since those are the reviewers

Table: xDocumentsUsers

DocID..UserID....Type...
1........386......approver
1........346......reviewer
1........394......reviewer..
1........519......reviewer..
4........408......reviewer..
5........408......reviewer..
6........408......reviewer..
7........386......approver..
7........111......readdone..
7........346......reviewer..
8........386......approver..
8........346......reviewer..
9........386......approver..
9........346......reviewer..
10.......386......approver..
11.......386......approver..
11......346......reviewer..
12......386......approver..
12......346......reviewer..
13......386......approver..
13......346......reviewer..
14......386......approver..
14......346......reviewer..
15......386......approver

So desired result would be...

DocID..UserID................Type...

1........386....................approver
1........346,394,519......reviewer.
4........408....................reviewer..
5........408....................reviewer..
6........408....................reviewer..
7........386....................approver..
7........111....................readdone..
7........346....................reviewer..
8........386....................approver..
8........346....................reviewer..
9........386....................approver..
9........346....................reviewer..
10......386....................approver..
11......386....................approver..
11......346....................reviewer..
12......386....................approver..
12......346....................reviewer..
13......386....................approver..
13......346....................reviewer..
14......386....................approver..
14......346....................reviewer..
15......386....................approver

DECLARE @UsersID varchar(100) SELECT @UsersID = COALESCE (@UsersID + ', ', '') + CAST(UserID AS varchar(5)) FROM xDocumentsUsers WHERE DocumentID = 1 SELECT @UsersID .............................................Gets me this result: 386, 346, 394, 519 — sjpizzle, Nov 29 '10 at 19:57

score 2 · Answer 1 · answered Oct 10 '12 at 13:22

The FOR XML PATH is a great solution. You need to be aware, though, that it will convert any special characters in the inner SELECTs result set into their xml equivalent - i.e., & will become & in the XML result set. You can easily revert back to the original character by using the REPLACE function around the inner result set. To borrow from astander's previous example, it would look like (note that the SELECT as the 1st argument to the REPLACE function is enclosed in ():

--Concat
SELECT  t.ID,
    REPLACE((SELECT  tIn.Val + ','
        FROM    @Table tIn
        WHERE   tIn.ID = t.ID
        FOR XML PATH('')), '&amp;', '&'))
FROM    @Table t
GROUP BY t.ID

score 1 · Answer 2 · edited May 23 '17 at 12:04

1

Have a look at

Emulating MySQL’s GROUP_CONCAT() Function in SQL Server 2005

Is there a way to create a SQL Server function to “join” multiple rows from a subquery into a single delimited field?

A simple example is

DECLARE @Table TABLE(
        ID INT,
        Val VARCHAR(50)
)
INSERT INTO @Table (ID,Val) SELECT 1, 'A'
INSERT INTO @Table (ID,Val) SELECT 1, 'B'
INSERT INTO @Table (ID,Val) SELECT 1, 'C'
INSERT INTO @Table (ID,Val) SELECT 2, 'B'
INSERT INTO @Table (ID,Val) SELECT 2, 'C'

--Concat
SELECT  t.ID,
        (
            SELECT  tIn.Val + ','
            FROM    @Table tIn
            WHERE   tIn.ID = t.ID
            FOR XML PATH('')
        )
FROM    @Table t
GROUP BY t.ID

edited May 23 '17 at 12:04

Community

1
1

answered Nov 29 '10 at 18:52

Adriaan Stander

162,879
31
289
284

I like the way this example works, if the table exists already would that mean I could just use what is after --Concat ? – sjpizzle Nov 29 '10 at 20:25
Yes, that is true. This is just an example, using a table variable. – Adriaan Stander Nov 30 '10 at 04:19

score 0 · Answer 3 · answered Nov 29 '10 at 18:54

0

Does this help?

SELECT DocID
     , [Type]
     , (SELECT CAST(UserID + ', ' AS VARCHAR(MAX)) 
         FROM [xDocumentsUsers] 
         WHERE (UserID = x1.UserID) 
         FOR XML PATH ('')
      ) AS [UserIDs]
FROM [xDocumentsUsers] AS x1

answered Nov 29 '10 at 18:54

p.campbell

98,673
67
256
322

--------------------------- Microsoft SQL Server Management Studio --------------------------- SQL Execution Error. Executed SQL statement: SELECT DocumentID, [Type], (SELECT CAST(UserID + ', ' AS VARCHAR(MAX)) FROM [xDocumentsUsers] WHERE (UserID = x1.UserID) FOR XML PATH('')) AS [UserIDs] FROM [xDocumentsUsers] AS x1 Error Source: .Net SqlClient Data Provider Error Message: Conversion failed when converting the varchar value ', ' to data type int. --------------------------- OK Help --------------------------- – sjpizzle Nov 29 '10 at 19:20

SQL Server query with multiple values in one column relating to another column

3 Answers3