SQL SELECT with JOINS and Multiple Rows for one Column

Question

The issue at hand is that I have a basic query (from a document software database), taking from multiple tables with some LEFT JOINS, but I have one column in question that needs to take from a table where there are multiple results per unique document id (DocGUID).

CURRENT QUERY

SELECT  doc.[Doc #]
        , '' AS 'Authors'
        , ud.[Lead Author]
        , doc.[Title]
        , ud.[Publication]
        , ud.[Citation]
        , ud.[Year]
        , ud.[Month]
        , ud.[Comments]
        , notes.[Note]
FROM    [tblDocuments] doc
LEFT JOIN [tblNotes] notes ON notes.[DocGUID] = doc.[DocGUID]
LEFT JOIN [tblUserData] ud ON ud.[MasterGUID] = doc.[DocGUID]
WHERE doc.[DocGUID] = '12345678'

As you can see, I have simply queried '' for "Authors". Here's where my issue comes in. I have a table named tblMultiValues where there are two or more authors listed per DocGUID.

Table Example: (for tblMultiValues)

|------|-------------|-------------|-------------------|
|  Id  |   DocGUID   |  FieldName  |       Value       |
|------|-------------|-------------|-------------------|
| 123  |   12345678  |   Authors   |   Collins, Nick   |
| 456  |   12345678  |   Authors   |  Williams, Robert |
| 321  |   87654321  |   Authors   |    Smith, Kate    |
| 654  |   87654321  |   Authors   |    Hanks, Tom     |
|------|-------------|-------------|-------------------|

So, what I want to show for the 2nd column of 'Authors', is the result of:

Collins, Nick; Williams, Robert

Specifically for DocGUID of '12345678'

How might one go about doing this, mixed in with the query that is already built?

(I hope this was enough info... if more is needed, please advise).

-Nick

:::EDIT:::

I was able to get things running with the following code... (very well guided from the answer given by @mohan111

SELECT  DISTINCT
    STUFF((
        SELECT  '; ' + mv2.Value
        FROM    [dbo].[tblMultiValues] mv2
        WHERE   mv1.DocGUID = mv2.DocGUID
        FOR XML PATH ('')),1,2,'') AS 'Authors', mv1.FieldName, mv1.DocGUID
INTO    #TempMultival
FROM    [dbo].[tblMultiValues] mv1

SELECT  doc.[Doc #]
        , tmv.[Authors]
        , ud.[Lead Author]
        , doc.[Title]
        , ud.[Publication]
        , ud.[Citation]
        , ud.[Year]
        , ud.[Month]
        , ud.[Comments]
        , notes.[Note]
FROM    [tblDocuments] doc
LEFT JOIN [tblNotes] notes ON notes.[DocGUID] = doc.[DocGUID]
LEFT JOIN [tblUserData] ud ON ud.[MasterGUID] = doc.[DocGUID]
LEFT JOIN #TempMultiVal tmv ON tmv.DoCGUID = doc.[DocGUID]

DROP TABLE #TempMultiVal

possible duplicate of [SQL group\_concat function in SQL Server](http://stackoverflow.com/questions/8868604/sql-group-concat-function-in-sql-server) — lc., Jun 17 '14 at 15:25
if it is based on DocGuid we can achieve the expected result or if it is based on ID then we will get 2 rows — mohan111, Jun 17 '14 at 15:34
It is based off of DocGUID (the "ID" field is part of the table, so I didn't ignore it in the example... sorry for the confusion). — NCollinsTE, Jun 17 '14 at 15:36

score 2 · Accepted Answer · answered Jun 17 '14 at 15:39

2

Declare @table TABLE 
(
    Id INT, 
    DocGUID    int,
    FieldName VARCHAR(25),
    Value VARCHAR(200)
);

INSERT INTO @table
(   Id, 
    DocGUID,
    FieldName,
    Value

)
VALUES
    (123,12345678,'Authors','Collins, Nick'),
    (456,12345678,'Authors','Williams, Robert'),
    (321,87654321,'Authors','Smith, Kate'),
    (654,87654321,'Authors','Hanks, Tom');
    Select distinct DocGUID,
    (SELECT 
    Substring((SELECT ', ' + CAST(i.id AS VARCHAR(1024)) 
    FROM   
    @table i 
    WHERE  i.DocGUID = tt.DocGUID 
    ORDER  BY i.id 
    FOR XML PATH('')), 3, 10000000) AS list) AS ID,
    FieldName,
    STUFF((Select distinct t.Value +  ',' 
        from @table t 
    where t.DocGUID = tt.DocGUID 
    FOR XML PATH(''),TYPE).value('.', 'NVARCHAR(MAX)') 
                        , 1,  0, ' ') from @table tt

answered Jun 17 '14 at 15:39

mohan111

8,633
4
28
55

Ok, this makes 100% sense, and I can use the temp table to feed my other query. But, how would I simply query the data from the dbo.tblMultiValues instead of stating the bogus values (DocGUID of 12345678, Kate Smith) etc.? – NCollinsTE Jun 17 '14 at 16:04
just add your table name in the place of @table and execute it :) – mohan111 Jun 17 '14 at 16:14
Thank you for your help! I had never used the FOR XML PATH code before. This led me far more than in the right direction. I have updated / edited my original post with the code I used that worked perfectly fine for me. Thanks again, @mohan111 – NCollinsTE Jun 17 '14 at 17:35
You might consider using [SQLFiddle](http://sqlfiddle.com/#!6/f400c/4) instead of using temp variables for test data. I find it confuses people less. – Conrad Frix Jun 17 '14 at 17:50

SQL SELECT with JOINS and Multiple Rows for one Column

1 Answers1