Wie kann man in SQL mehrere Zeilen mit mehreren Spalten dynamisch gruppieren und zusammenführen?

Dynamisches Gruppieren und Zusammenführen mehrerer Zeilen und Spalten in SQL

Beim Arbeiten mit tabellarischen Daten müssen Sie häufig Datensätze basierend auf einer oder mehreren Spalten gruppieren und die Ergebnisse in einer einzigen Zeile mit mehreren Spalten zusammenführen. Dies ist besonders nützlich, wenn es um mehrere Werte geht, die jeder Gruppe zugeordnet sind.

Angenommen, Sie haben eine Tabelle mit Testergebnissen, in der jede Zeile einen bestimmten Testtyp und das entsprechende Ergebnis darstellt. Beispielsweise könnte die Tabelle „Ergebnisse“ das folgende Schema haben:

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

und die folgenden Daten:

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)

Um diese Ergebnisse nach „TestType“ zu gruppieren und für jedes Ergebnis eine neue Zeile mit mehreren Spalten zu erstellen, können wir die Operation „Kreuztabelle“ oder „Pivot“ verwenden. Ein Ansatz besteht darin, die Funktion „ROW_NUMBER“ zu verwenden, um jedem Ergebnis innerhalb der Gruppe eine Sequenznummer zuzuweisen und dann die bedingte Aggregation zu verwenden, um die erforderlichen Werte zu extrahieren.

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;

Diese Abfrage erzeugt die folgende Ausgabe:

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

Diese Methode ist jedoch auf eine feste Anzahl von Ergebnissen beschränkt. Um eine dynamische Lösung zu erstellen, die eine beliebige Anzahl von Ergebnissen berücksichtigen kann, können wir eine Technik namens „Übergreifend angewendete Zähltabellen“ verwenden.

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;

Diese Abfrage verwendet eine „Zähltabelle“, um eine Folge von Ganzzahlen zu erstellen, die dynamisch eine SQL-Anweisung basierend auf der maximalen Anzahl von Ergebnissen in der Tabelle generiert. Mit dieser Technik können beliebig viele Ergebnisse in einer einzigen Zeile zusammengefasst werden.

Mit diesen Methoden können Sie Tabellendaten effektiv gruppieren und in einem strukturierteren und aussagekräftigeren Format zusammenführen und so die Datenanalyse und -visualisierung vereinfachen.

Das obige ist der detaillierte Inhalt vonWie kann man in SQL mehrere Zeilen mit mehreren Spalten dynamisch gruppieren und zusammenführen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!