如何使用基于分组列动态生成的列将多个 SQL 行合并为一个？

基于分组列动态生成列，合并多行SQL数据

问题：

如何按特定列分组数据，并将具有相同列值的多行合并为单行，同时生成多个列？具体来说，需要按“TestType”列分组，并将“Result”列拆分为每个匹配“TestType”值的单独列。

解答：

可以使用SQL Server中的交叉表（PIVOT）实现此目的。但是，如果每个组的列数是动态的（即可能变化），则需要更灵活的解决方案：

使用FOR XML PATH的动态解决方案：

创建带行号的CTE：

<code class="language-sql">WITH RNs AS (
    SELECT WorkOrder,
           TestType,
           Result,
           ROW_NUMBER() OVER (PARTITION BY WorkOrder, TestType ORDER BY (SELECT NULL)) AS RN
    FROM dbo.Result
)</code>

构建动态SQL语句：

<code class="language-sql">DECLARE @SQL nvarchar(MAX);
DECLARE @CRLF nvarchar(2) = CHAR(13) + CHAR(10); --回车换行符

-- 获取任何“TestType”的最大结果数
SELECT @MaxTally = MAX(C)
FROM (
    SELECT COUNT(*) AS C
    FROM dbo.Result
    GROUP BY WorkOrder,
             TestType
);

-- 创建具有最大行数的计数表
WITH Tally AS (
    SELECT TOP (@MaxTally) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N),
         (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)
)

-- 构建动态SQL语句
SELECT @SQL = N'WITH RNs AS (' + @CRLF +
              N'    SELECT WorkOrder, ' + @CRLF +
              N'           TestType, ' + @CRLF +
              N'           Result, ' + @CRLF +
              N'           ROW_NUMBER() OVER (PARTITION BY WorkOrder, TestType ORDER BY (SELECT NULL)) AS RN ' + @CRLF +
              N'    FROM dbo.Result)' + @CRLF +
              N'SELECT WorkOrder, ' + @CRLF +
              N'       TestType, ' + @CRLF +
              -- 使用FOR XML PATH动态生成列
              STUFF((SELECT N',' + @CRLF +
                            CONCAT(N'       MAX(CASE RN WHEN ',T.I,N' THEN Result END) AS Result',T.I)
                     FROM Tally T
                     ORDER BY T.I ASC
                     FOR XML PATH(N''), TYPE).value('(./text())[1]','nvarchar(MAX)'),1,3,N'') + @CRLF +
              N'FROM RNs R ' + @CRLF +
              N'GROUP BY WorkOrder, ' + @CRLF +
              N'         TestType;'</code>

执行动态SQL语句：

<code class="language-sql">PRINT @SQL; -- 查看SQL语句

EXEC sys.sp_executesql @SQL;</code>

此解决方案将根据每个“TestType”的最大结果数动态生成列，从而得到所需输出。 代码中添加了 @CRLF 变量用于更好的代码可读性，并增强了跨平台兼容性。

以上是如何使用基于分组列动态生成的列将多个 SQL 行合并为一个？的详细内容。更多信息请关注PHP中文网其他相关文章！