sql-server - SQL Server 透视到未知数量的列

Question

我有这样的数据:

   id    | serialNo
 0245DS6 | 05813542
 0245DS6 | 05813543
 0245DS6 | 05813544
 2231VC7 | 06885213
 5432PS1 | 01325131
 5432PS1 | 01325132

我需要这样输出:

    id    | serial_1 | serial_2 | serial_3 | ...
  0245DS6 | 05813542 | 05813543 | 05813544 | ...
  2231VC7 | 06885213 |          |          | 
  5432PS1 | 01325131 | 01325132 |          | 

我不知道每个 id 有多少个序列号(很可能不会超过 10 个)，并且每个 id 的数量各不相同。我认为 pivot 是我需要使用的，但我对 SQL 的了解还不够，无法知道其他问题的哪些答案对我有用，或者这是否可能。

Answer 1

我只想说这会很有趣(也有点邪恶)。

表结构:

CREATE TABLE #temp
(
    id   VARCHAR(100), 
    serialNo VARCHAR(100)
);

测试数据

INSERT INTO #temp
VALUES
('0245DS6','05813542'),
('0245DS6','05813543'),
('0245DS6','05813544'),
('2231VC7','06885213'),
('5432PS1','01325131'),
('5432PS1','01325132')

然后获取独特的组:

DECLARE @columns VARCHAR(MAX)=
(
    STUFF(
    (
    Select ','+QUOTENAME(CAST(rowId AS VARCHAR(100))) AS [text()]
    FROM
    (
        SELECT DISTINCT 
            ROW_NUMBER() OVER(PARTITION BY id order by serialNo) AS rowId
        FROM #temp
    ) as tbl
    For XML PATH ('')
    )
    ,1,1,'')
)

然后执行动态数据透视:

DECLARE @query NVARCHAR(MAX)='SELECT
    *
FROM
(
    SELECT
        ROW_NUMBER() OVER(PARTITION BY id order by serialNo) as rowId,
        id,
        serialNo
    FROM
        #temp 
)AS sourceTable 
PIVOT
(
    MAX(serialNo)
    FOR rowId IN ('+@columns+')
) AS pvt'

EXECUTE sp_executesql @query

结果:

Id        1           2           3
-------------------------------------------
0245DS6   05813542    05813543    05813544
2231VC7   06885213    NULL        NULL
5432PS1   01325131    01325132    NULL