Comment regrouper et fusionner dynamiquement plusieurs lignes avec plusieurs colonnes dans SQL ?

Regroupement dynamique et fusion de plusieurs lignes et colonnes en SQL

Lorsque vous travaillez avec des données tabulaires, vous devez souvent regrouper les enregistrements en fonction d'une ou plusieurs colonnes et fusionner les résultats en une seule ligne avec plusieurs colonnes. Ceci est particulièrement utile lorsqu'il s'agit de plusieurs valeurs associées à chaque groupe.

Supposons que vous ayez un tableau contenant les résultats des tests, où chaque ligne représente un type de test spécifique et son résultat correspondant. Par exemple, le tableau « Résultats » pourrait avoir le schéma suivant :

CREATE TABLE Result(
  WorkOrder varchar(10),
  TestType varchar(20),
  Result decimal(10,2)
);

et les données suivantes :

INSERT INTO Result (WorkOrder, TestType, Result) VALUES 
('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)

Afin de regrouper ces résultats par "TestType" et de créer une nouvelle ligne avec plusieurs colonnes pour chaque résultat, nous pouvons utiliser l'opération "Crosstab" ou "Pivot". Une approche consiste à utiliser la fonction « ROW_NUMBER » pour attribuer un numéro de séquence à chaque résultat au sein du groupe, puis à utiliser l'agrégation conditionnelle pour extraire les valeurs requises.

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;

Cette requête produira le résultat suivant :

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

Cependant, cette méthode est limitée à un nombre fixe de résultats. Pour créer une solution dynamique pouvant prendre en charge n'importe quel nombre de résultats, nous pouvons utiliser une technique appelée « tableaux de comptage appliqués de manière croisée ».

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 --ORDER BY 应为您的 ID/始终递增列' + @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;

Cette requête utilise une « table de comptage » pour créer une séquence d'entiers qui génère dynamiquement une instruction SQL basée sur le nombre maximum de résultats dans la table. Cette technique permet de combiner n'importe quel nombre de résultats en une seule ligne.

En utilisant ces méthodes, vous pouvez regrouper et fusionner efficacement les données tabulaires dans un format plus structuré et significatif, simplifiant ainsi l'analyse et la visualisation des données.

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!