将SQL中的输出合并为一行

Question

我正在尝试编写一个SQL查询，它使用一个ID来选择一组相关的行。但是，我希望将它们连接到一行中，而不是为每个关联的项单独一行。

例如，而不是：

ID    Item
1     a
2     b
1     c

我想：

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

目前，我正在使用一个简单的查询，但不知道如何执行此操作：

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

这个查询是一个大得多的查询的一部分，这个查询涉及多个连接和一个联合。最终，所有这些都将被转换为XML格式，希望类似于以下内容：

<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>

Answer 1

我花了一些力气才做到这一点，但这应该是可行的

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

Answer 2

我最终使用子查询。我确实看了你的答案，但我不理解它，也不能让它工作。这是我三年前上大学以来第一次接触SQL，所以我很生疏。这对我来说至少适用于一个表查询：

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

它会产生如下输出：

<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>

现在来看下一个联合问题，它导致我不能使用xml数据类型:P