连接行和用特殊字符分隔SQL Server

Question

我有下面的表格，经过排序。

ID    Value     Amount
A       2        10.00
A       3         4.25 
B       6         2.01
B       7         5.00 
B       9         9.00
B       11        4.25
C       8         10.00
C       6         4.00

如何获取：

ID    Col1           Col2
A    [2-3]          [10.00-4.25]
B    [6-7],[9-11]   [2.01-5.00], [9.00-4.25]
C    [8-6]          [10.00-4.00]  

如果我只用逗号分隔，我知道这可以使用FOR XML PATH，但是我不知道如何只为成对的值插入。

Answer 1

您可以使用ROW_NUMBER将它们按两个分组。这个想法是先用-连接行，然后用,再次连接它们。

;WITH CteGrouping AS(
    SELECT *,
        RN = (ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Value) + 1) / 2
    FROM SampleData
)
,CteDash AS(
    SELECT
        ID,
        Col1 = STUFF((
            SELECT '-' + CONVERT(VARCHAR(10), Value)
            FROM CteGrouping t2
            WHERE 
                t2.ID = t1.ID
                AND t2.RN = t1.RN
            FOR XML PATH('')
        ),1 , 1, ''),
        Col2 = STUFF((
            SELECT '-' + CONVERT(VARCHAR(10), Amount)
            FROM CteGrouping t2
            WHERE 
                t2.ID = t1.ID
                AND t2.RN = t1.RN
            FOR XML PATH('')
        ),1, 1, '')
    FROM CteGrouping t1
    GROUP BY t1.ID, t1.RN
)
,CteBracket AS(
    SELECT
        ID,
        Col1 = STUFF((
            SELECT ',' + '[' + Col1 + ']'
            FROM CteDash t2
            WHERE 
                t2.ID = t1.ID
            FOR XML PATH('')
        ),1 , 1, ''),
        Col2 = STUFF((
            SELECT ',' + '[' + Col2 + ']'
            FROM CteDash t2
            WHERE 
                t2.ID = t1.ID
            FOR XML PATH('')
        ),1, 1, '')
    FROM CteDash t1
    GROUP BY t1.ID
)
SELECT * FROM CteBracket

结果

| ID |         COL1 |                    COL2 |
|----|--------------|-------------------------|
|  A |        [2-3] |            [10.00-4.25] |
|  B | [6-7],[9-11] | [2.01-5.00],[9.00-4.25] |
|  C |        [6-8] |            [4.00-10.00] |

请参阅SQL FIDDDLE。