Flattening multiple rows

Question

I'm trying to combine the results of multiple rows into one. I would like to flatten the first two rows below, and perhaps combine the keywords so that they are in the same column. How can I do that simply using a select statement (i.e. no functions)?

I'm currently getting:

documentid documentname keyword
1          doc1         politics politics italy 
2          doc2         politics politics italy

I would like to get:

documentid documentname keyword
1          doc1         politics italy
2          doc2         politics

This is part of my query:

SELECT  d.DocumentId AS documentid ,
    m.Title AS documentname ,
    STUFF(( SELECT  N' ' + k.Word
        FROM    [arabicarchive].[dbo].[Keywords] k
            JOIN [arabicarchive].[dbo].DocumentKeywords dk ON k.KeywordId = dk.Keyword_KeywordId
            JOIN [arabicarchive].[dbo].Documents d ON dk.Document_DocumentId = d.DocumentId
        FOR
            XML PATH('')
        ), 1, 1, '') AS Keyword
FROM    [arabicarchive].[dbo].[Metadatas] m
    JOIN [arabicarchive].[dbo].[Documents] d ON d.DocumentId = m.DocumentId
WHERE   d.Status = 1

EDIT: I have updated the query and the results that I am currently getting. I haven't used STUFF or XML PATH before so please bear with me.

EDIT 2: I have managed to get rid of the duplicate row, but the result in the keyword column is not correct.

EDIT 3: Adding DISTINCT to the query still doesn't produce a keyword column with the correct values.

possible duplicate of [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) — Taryn, Feb 04 '14 at 11:09
Ahh...I didn't know what it was called as pivot and coalesce didn't seem to be what I wanted. I've edited the question as I haven't gotten it to work... — Tums, Feb 04 '14 at 11:31
You probably don't need the STUFF function since that is only used to take out the first delimiter and an LTRIM would do this for you if you are using a space as a delimiter. You would get rid of the duplicate tags by means of DISTINCT. — Phil Factor, Feb 04 '14 at 13:02
I tried putting a distinct in the query, but it still doesn't work. The issue I'm having is the values in the keyword column are not correct. — Tums, Feb 04 '14 at 13:20
You've used the same alias in the correlated subquery as you have in the main query which is doing you no good at all. You don't need the thired join in the correlated subquery I reckon. Try out the code I've put in my answer. — Phil Factor, Feb 04 '14 at 13:34

Phil Factor · Accepted Answer · 2014-02-04T13:32:08.273

In an article I once wrote, towards the end, I showed how to do this Database Migration Scripts: Getting from place A to place B If you look near the end, you'll see some code that is similar to what you want. I've modified to fit what you want a bit more closely.

    SELECT title_ID, title, ltrim(
    (SELECT distinct ' '+tagname.tag FROM titles thisTitle
      INNER JOIN TagTitle ON titles.title_ID=TagTitle.Title_ID
      INNER JOIN Tagname ON Tagname.TagName_ID=TagTitle.TagName_ID
    WHERE ThisTitle.title_id=titles.title_ID
    FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'))  
    FROM titles
    ORDER BY title_ID

I reckon that something like this would work but I've no means of testing it!...

    SELECT  d.DocumentId AS documentid ,  m.Title AS documentname ,
           ltrim(
              ( SELECT  distinct N' ' + k.Word
            FROM    [arabicarchive].[dbo].[Keywords] k
                JOIN [arabicarchive].[dbo].DocumentKeywords dk ON k.KeywordId = dk.Keyword_KeywordId
                Where  dk.Document_DocumentId=d.DocumentId
            FOR
                XML PATH(''), TYPE).value('.', 'nvarchar(max)')
            ) AS Keyword
    FROM    [arabicarchive].[dbo].[Metadatas] m
        JOIN [arabicarchive].[dbo].[Documents] d ON d.DocumentId = m.DocumentId
    WHERE   d.Status = 1

I was fiddling around with your first answer, without luck. Refresh and voila! Your answer works great! Thank you!!! — Tums, Feb 04 '14 at 13:58

Flattening multiple rows

1 Answers1