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