CODEWITHSUNDEEP
sql-server
Zoosmell
Zoosmell

Reputation: 149

Combine output in SQL into single row

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>

Upvotes: 1

Views: 191

Answers (2)

Zoosmell
Zoosmell

Reputation: 149

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

Upvotes: 1

ThePravinDeshmukh
ThePravinDeshmukh

Reputation: 1913

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

Upvotes: 1

Related Questions