1

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>
Zoosmell
  • 149
  • 1
  • 1
  • 13
  • I'm not totally sure, but this looks like a case for a pivot. – Evan Frisch May 21 '15 at 22:13
  • 1
    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. http://stackoverflow.com/questions/11617713/how-to-create-a-pivottable-in-transact-sql – JBond May 21 '15 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 May 21 '15 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 May 22 '15 at 00:09
  • Unfortunately not, the XML is generated from the query so it would just make them all one XML field. Thank you though. – Zoosmell May 22 '15 at 02:21

2 Answers2

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
ThePravinDeshmukh
  • 1,823
  • 12
  • 21
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

Zoosmell
  • 149
  • 1
  • 1
  • 13