SQL服务器：通过字符串连接组(SQL Server : Group by string conca

我有个问题。我知道，已经被问过。我通过相关的问题看了，但我不能让我的SQL脚本工作。

这里是我的查询：

SELECT T1.PART_ID, T2.ID, T2.DESCRIPTION
FROM #TEMP T1 
INNER JOIN #TEMP2 T2 ON T1.PART_ID = T2.PART_ID
ORDER BY T2.ID

表：

PART_ID |  ID        |    DESCRIPTION
----------------------------------
10002   |  1182505   |   Tagfahrlichtschaltung
80029   | 1182505    |   Bluetooth
20004   | 1212866    |    Kindersitzbefestigung
10045   |  1212866   |    Lederlenkradrriegelung
11908   |  1257946   |    Airbag
22346   | 1257946    |    Automatic

我想有结果，如：

ID       | LISTOFPARTS
-----------------------------
1182505  |  "10002 : Tagfahrlichtschaltung ; 80029 : Bluetooth  "
1212866  |  "20004 : Kindersitzbefestigung ; 10045 : Lederlenkradrriegelung"
1257946  |  "11908 : AIRBAG ; 22346 : AUTOMATIC"

我想它必须是一些与XML路径，但我无法得到它的工作。任何人都可以重写查询，以便它返回的结果分组，并在字符串连接起来？

带有或不带有XML路径解决方案将不胜感激。

谢谢！

Answer 1:

这将工作 -

DECLARE @TABLE TABLE (PART_ID INT,ID INT, DESCRIPTION VARCHAR(100))

INSERT INTO @TABLE 
VALUES
(10002   ,1182505   ,   'Tagfahrlichtschaltung')
,(80029   , 1182505    ,   'Bluetooth')
,(20004   , 1212866    ,    'Kindersitzbefestigung')
,(10045   ,  1212866   ,    'Lederlenkradrriegelung')
,(11908   ,  1257946   ,    'Airbag')
,(22346   , 1257946    ,    'Automatic')

;WITH SUBQUERY
AS
(
    SELECT ID,(CAST(PART_ID AS VARCHAR(12)) + ' : ' + DESCRIPTION) 'CONCATED'
    FROM @TABLE 
)

SELECT ID, LEFT(pre_trimmed.CONCATED , LEN(pre_trimmed.CONCATED )-1) AS LISTOFPARTS
FROM SUBQUERY AS extern
CROSS APPLY
(
    SELECT CONCATED + ','
    FROM SUBQUERY AS intern
    WHERE extern.ID = intern.ID
    FOR XML PATH('')
) pre_trimmed (CONCATED)
GROUP BY ID, pre_trimmed.CONCATED

Answer 2:

http://sqlfiddle.com/#!3/d41d8/5441

create table #Temp (PART_ID bigint, ID bigint, DESCRIPTION nvarchar(max))

insert into #Temp
select 10002, 1182505, 'Tagfahrlichtschaltung' union all
select 80029, 1182505, 'Bluetooth' union all
select 20004, 1212866, 'Kindersitzbefestigung' union all
select 10045, 1212866, 'Lederlenkradrriegelung' union all
select 11908, 1257946, 'Airbag' union all
select 22346, 1257946, 'Automatic'

select 
    T1.ID,
    stuff(
        (
            select ' ; ' + cast(T2.PART_ID as nvarchar(max)) + ' : ' + T2.DESCRIPTION
            from #TEmp as T2
            where T2.ID = T1.ID 
            for xml path(''), type
        ).value('data(.)', 'nvarchar(max)')
    , 1, 3, '') as LISTOFPARTS
from #TEMP as T1 
group by T1.ID
order by T1.ID

文章来源: SQL Server : Group by string concatenation