如何在MSSQL中动态转置由openjson生成的表格？

要实现将每行的Begin Fixation和End Fixation转换为一组列的需求，你需要先给原始数据标记行号，再通过动态SQL+PIVOT实现转置——因为行数不固定，静态SQL无法适配所有场景。

步骤1：准备数据 #

假设你的OPENJSON结果存储在临时表#Temp中（可直接替换为OPENJSON查询）：

CREATE TABLE #Temp (
    [Begin Fixation] DATE,
    [End Fixation] DATE
)

INSERT INTO #Temp VALUES
('2011-11-01', '2011-11-10'),
('2011-11-01', '2011-11-17'),
('2011-11-01', '2011-11-29'),
('2011-11-11', '2011-11-29'),
('2011-11-14', '2011-11-29'),
('2011-11-18', '2011-11-29')

步骤2：动态SQL实现转置 #

核心思路：先给每行加唯一行号，再通过CROSS APPLY将每行的两个字段拆分为两行，最后用PIVOT转置为目标列格式：

DECLARE @cols NVARCHAR(MAX), @pivotCols NVARCHAR(MAX), @query NVARCHAR(MAX)

-- 生成最终需要的列名（如[1.Begin fixation], [1.End fixation]）
SELECT @cols = STRING_AGG(
    CONCAT('[', RowNum, '.Begin fixation], [', RowNum, '.End fixation]'), ', '
)
FROM (SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum FROM #Temp) t

-- 生成PIVOT需要的中间列名（如[1 Begin fixation], [1 End fixation]）
SELECT @pivotCols = STRING_AGG(
    CONCAT('[', RowNum, ' Begin fixation], [', RowNum, ' End fixation]'), ', '
)
FROM (SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum FROM #Temp) t

-- 构建并执行动态查询
SET @query = CONCAT('
SELECT ', @cols, '
FROM (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum,
        [Begin Fixation],
        [End Fixation]
    FROM #Temp  -- 替换为你的OPENJSON查询
) src
CROSS APPLY (
    VALUES 
        (CONCAT(RowNum, '' Begin fixation''), CAST([Begin Fixation] AS VARCHAR(10))),
        (CONCAT(RowNum, '' End fixation''), CAST([End Fixation] AS VARCHAR(10)))
) unpvt (ColName, ColValue)
PIVOT (
    MAX(ColValue)
    FOR ColName IN (', @pivotCols, ')
) pvt
')

EXEC sp_executesql @query

关键说明 #

1. 行号标记：用ROW_NUMBER()给每行分配唯一序号，确保每组列能对应到原始行。
2. 拆分行数据：CROSS APPLY将每行的两个日期字段拆分为两行，为PIVOT转置做准备。
3. 动态列生成：STRING_AGG自动生成所有需要的列名，适配任意行数的原始数据。

如果要直接使用OPENJSON的结果，只需将FROM #Temp替换为你的OPENJSON查询，例如：

FROM OPENJSON(@yourJsonString)
WITH (
    [Begin Fixation] DATE '$.BeginFixation',
    [End Fixation] DATE '$.EndFixation'
)

内容的提问来源于stack exchange，提问作者sergeusprecious