Combine output in SQL into single row

Question

I am trying to write a SQL Query that uses an ID to select a bunch of related rows. However, rather than a single row for each of the associated items I would like to concatenate them into a single row.

For example rather than:

ID    Item
1     a
2     b
1     c

I would like:

ID    Item 1    Item 2    ...
1     a         c
2     b

Currently I am using a simple query but can't figure out how to do this:

Select
    Investigation.ID,
    Record.Item
FROM 
    Investigation
INNER JOIN 
    Record
    ON Investigation.ID = Record.Inv_Id

This query is all part of a much larger one that involves multiple joins and a union. Eventually this will all be converted to XML in a format that hopefully resembles something like this:

<investigation>
    <id>1</id>
    <name>something</name>
    <items>
        <item>a</item>
        <item>c</item>
    </items>
<investigation>
<investigation>
    <id>2</id>
    <name>something else</name>
    <items>
        <item>b</item>
    </items>
</investigation>

I'm not totally sure, but this looks like a case for a pivot. — Evan Frisch
– Evan Frisch, Commented May 21, 2015 at 22:13
Please take a look at the following question. It uses the PIVOT and will get the results you require. Also explaining how to create a dynamic pivot statement if your values are not always known. stackoverflow.com/questions/11617713/… — JBond
– JBond, Commented May 21, 2015 at 22:42
I'll look into just a bit worried about performance as it would have to be a dynamic pivot included in an already complex sql query. Thank you :) — Zoosmell
– Zoosmell, Commented May 21, 2015 at 23:54
does to work for you if it returns the items as comma separated? if you have dynamic number of "Items" then "STUFF" and "coalesce" could work for you... let me know if that works so that i give you the solution — Aram
– Aram, Commented May 22, 2015 at 0:09
Unfortunately not, the XML is generated from the query so it would just make them all one XML field. Thank you though. — Zoosmell
– Zoosmell, Commented May 22, 2015 at 2:21

ThePravinDeshmukh · Accepted Answer · 2015-05-22 06:03:02Z

1

Took some efforts to do this But this should work

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.[Id]) 
            FROM [Investigation] c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = 'SELECT [Id], ' + @cols + ' from 
            (
                select id, item, ROW_NUMBER() over (PARTITION BY id order by item asc) as uid
                from [Investigation]
           ) x
            pivot 
            (
                MIN(item)
                for [uid] in (' + @cols + ')
            ) p '


execute(@query)

print @query

edited May 22, 2015 at 6:03

answered May 22, 2015 at 5:56

ThePravinDeshmukh

1,92313 silver badges23 bronze badges

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

Comments

Zoosmell · Accepted Answer · 2015-05-25 00:29:51Z

1

I ended up using a subquery. I did look at your answer Pravin but I didn't understand it and couldn't make it work. This is my first time dipping into SQL since three years ago at university so I'm very rusty. This worked for me at least for a single table query:

SELECT ( SELECT * FROM ( 
    Select Investigation.ID as ID,
        (SELECT ( SELECT * FROM ( select
            Record.item
            FROM Investigation as Inv
            INNER JOIN Record
            ON Inv.ID = Record.Inv_Id
            WHERE Inv.ID = Investigations.ID
        )
        AS temp FOR xml path(''), type )
    ) AS Items
from dbo.Investigation
) AS xmlextract FOR xml path ('Investigation'), 
root('root') )AS xmlextract

It produces output like this:

<root>
    <Investigation>
        <ID>1</ID>
        <Items>
            <item>a</item> 
            <item>c</item>
        </Items>
    </Investigation>
    <Investigation>
        <ID>2</ID>
        <Items>
            <item>b</item>
        </Items>
    </Investigation>
</root>

Now onto the next problem of the union causing me to be unable to use the xml data type :P

edited May 25, 2015 at 0:29

answered May 24, 2015 at 23:57

Zoosmell

1491 gold badge1 silver badge14 bronze badges

1 Comment

ThePravinDeshmukh Over a year ago

Can you please share the outcome you got with my query ?

Collectives™ on Stack Overflow

Combine output in SQL into single row

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related