Maison > base de données > tutoriel mysql > Comment regrouper et fusionner dynamiquement plusieurs lignes avec plusieurs colonnes dans SQL ?

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

Susan Sarandon
Libérer: 2025-01-22 01:02:08
original
932 Les gens l'ont consulté

How to Dynamically Group and Merge Multiple Rows with Multiple Columns in 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 :

<code class="language-sql">CREATE TABLE Result(
  WorkOrder varchar(10),
  TestType varchar(20),
  Result decimal(10,2)
);</code>
Copier après la connexion

et les données suivantes :

<code class="language-sql">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)</code>
Copier après la connexion

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.

<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>
Copier après la connexion

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>
Copier après la connexion

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

<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 --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;</code>
Copier après la connexion

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!

source:php.cn
Déclaration de ce site Web
Le contenu de cet article est volontairement contribué par les internautes et les droits d'auteur appartiennent à l'auteur original. Ce site n'assume aucune responsabilité légale correspondante. Si vous trouvez un contenu suspecté de plagiat ou de contrefaçon, veuillez contacter admin@php.cn
Derniers articles par auteur
Tutoriels populaires
Plus>
Derniers téléchargements
Plus>
effets Web
Code source du site Web
Matériel du site Web
Modèle frontal