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
– sjpizzle, Commented Nov 29, 2010 at 19:57

tw4um · Accepted Answer · 2012-10-10 13:22:27Z

2

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

answered Oct 10, 2012 at 13:22

tw4um

212 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:04:14Z

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, 2017 at 12:04

CommunityBot

11 silver badge

answered Nov 29, 2010 at 18:52

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

2 Comments

sjpizzle Over a year ago

I like the way this example works, if the table exists already would that mean I could just use what is after --Concat ?

Adriaan Stander Over a year ago

Yes, that is true. This is just an example, using a table variable.

p.campbell · Accepted Answer · 2010-11-29 18:54:52Z

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, 2010 at 18:54

p.campbell

101k70 gold badges264 silver badges326 bronze badges

1 Comment

sjpizzle Over a year ago

--------------------------- 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 ---------------------------

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related