Comment fusionner plusieurs lignes SQL en une seule avec des colonnes générées dynamiquement basées sur une colonne de regroupement ?

Générez dynamiquement des colonnes en fonction du regroupement de colonnes et fusionnez plusieurs lignes de données SQL

Question :

Comment regrouper les données par colonne spécifique et fusionner plusieurs lignes avec la même valeur de colonne en une seule ligne, générant ainsi plusieurs colonnes en même temps ? Plus précisément, vous devez regrouper par colonne « TestType » et diviser la colonne « Résultat » en une colonne distincte pour chaque valeur « TestType » correspondante.

Réponse :

Cela peut être réalisé en utilisant le Tableau croisé (PIVOT) dans SQL Server. Cependant, si le nombre de colonnes par groupe est dynamique (c'est-à-dire peut changer), une solution plus flexible est requise :

Solution dynamique utilisant FOR XML PATH :

Créer un CTE avec le numéro de ligne :

<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>

Créez une instruction SQL dynamique :

<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>

Exécuter l'instruction SQL dynamique :

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

EXEC sys.sp_executesql @SQL;</code>

Cette solution générera dynamiquement des colonnes basées sur le nombre maximum de résultats pour chaque "TestType", ce qui donnera le résultat souhaité. @CRLF des variables ont été ajoutées au code pour une meilleure lisibilité du code et une compatibilité multiplateforme améliorée.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!