Comment regrouper des colonnes et combiner plusieurs lignes en une seule ligne avec plusieurs colonnes en SQL ?

Regroupez les colonnes et fusionnez plusieurs lignes en une seule ligne avec plusieurs colonnes dans SQL Server

Dans certains cas, vous souhaiterez peut-être regrouper les données par colonnes spécifiques et regrouper plusieurs valeurs de lignes associées en une seule ligne avec plusieurs colonnes. Prenons comme exemple la situation suivante :

Vous disposez d'un tableau appelé Résultat qui contient les colonnes suivantes :

• WorkOrder : Identifiant d'une commande spécifique
• TestType : Type de test exécuté
• Résultat : résultat du test

Les données du tableau Résultat ressemblent à ceci :

WorkOrder	TestType	Result
HP19002316	VitaminA	10.3
HP19002316	VitaminA	11.3
HP19002316	VitaminA	12.3
HP19002316	VitaminB	13.4
HP19002316	VitaminB	14.4
HP19002316	VitaminC	15.5
HP19002316	VitaminD	17.0

Vous souhaitez reformater les données dans la structure suivante :

WorkOrder	TestType	Result1	Result2	Result3
HP19002316	VitaminA	10.3	11.3	12.3
HP19002316	VitaminB	13.4	14.4	NULL
HP19002316	VitaminC	15.5	NULL	NULL
HP19002316	VitaminD	17.0	NULL	NULL

Le défi ici est de regrouper les résultats par TestType et de combiner les multiples valeurs de résultat dans des colonnes distinctes, étiquetées Result1, Result2, etc.

Solution non dynamique

Pour un nombre fixe de résultats, vous pouvez utiliser une approche simple :

<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
)
SELECT WorkOrder,
       TestType,
       MAX(CASE RN WHEN 1 THEN Result END) AS Result1,
       MAX(CASE RN WHEN 2 THEN Result END) AS Result2,
       MAX(CASE RN WHEN 3 THEN Result END) AS Result3
FROM RNs R
GROUP BY WorkOrder,
         TestType;</code>

Cette requête limite les résultats à trois colonnes, Result1, Result2 et Result3. Cependant, pour des quantités de résultats dynamiques, une solution plus complexe est requise.

Solutions dynamiques

Pour gérer un nombre incertain de résultats, vous pouvez utiliser une requête SQL dynamique qui crée automatiquement les colonnes nécessaires :

<code class="language-sql">DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10),
        @MaxTally int;

SELECT @MaxTally = MAX(C)
FROM (SELECT COUNT(*) AS C
      FROM dbo.Result
      GROUP BY WorkOrder,
               TestType) R;

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT TOP (@MaxTally) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2)
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 +
              --由于不知道 SQL Server 版本，因此使用 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;';

PRINT @SQL; --您的好朋友。

EXEC sys.sp_executesql @SQL;</code>

Cette requête génère une instruction SQL dynamique qui crée un nombre suffisant de colonnes de résultats en fonction du nombre maximum de résultats pour n'importe quel TestType. Il utilise un CTE (Common Table Expression) appelé Tally pour générer dynamiquement le numéro de colonne de la colonne Résultat.

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!