Bagaimana untuk Menghimpun dan Menggabungkan Berbilang Baris secara Dinamik dengan Berbilang Lajur dalam SQL?

Pengumpulan dinamik dan penggabungan berbilang baris dan lajur dalam SQL

Apabila bekerja dengan data jadual, anda selalunya perlu mengumpulkan rekod berdasarkan satu atau lebih lajur dan menggabungkan hasil menjadi satu baris dengan berbilang lajur. Ini amat berguna apabila berurusan dengan pelbagai nilai yang dikaitkan dengan setiap kumpulan.

Andaikan anda mempunyai jadual yang mengandungi keputusan ujian, di mana setiap baris mewakili jenis ujian tertentu dan hasil yang sepadan. Sebagai contoh, jadual "Keputusan" mungkin mempunyai skema berikut:

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

dan data berikut:

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

Untuk mengumpulkan hasil ini mengikut "TestType" dan mencipta baris baharu dengan berbilang lajur untuk setiap hasil, kami boleh menggunakan operasi "Crosstab" atau "Pivot". Satu pendekatan ialah menggunakan fungsi "ROW_NUMBER" untuk memberikan nombor jujukan kepada setiap hasil dalam kumpulan dan kemudian menggunakan pengagregatan bersyarat untuk mengekstrak nilai yang diperlukan.

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

Pertanyaan ini akan menghasilkan output berikut:

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

Walau bagaimanapun, kaedah ini terhad kepada bilangan keputusan tetap. Untuk mencipta penyelesaian dinamik yang boleh menampung sebarang bilangan hasil, kami boleh menggunakan teknik yang dipanggil "jadual pengiraan gunaan silang."

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

Pertanyaan ini menggunakan "jadual kira" untuk mencipta jujukan integer yang menjana pernyataan SQL secara dinamik berdasarkan bilangan maksimum hasil dalam jadual. Teknik ini membolehkan sebarang bilangan hasil digabungkan menjadi satu baris.

Dengan menggunakan kaedah ini, anda boleh mengumpulkan dan menggabungkan data jadual dengan berkesan ke dalam format yang lebih berstruktur dan bermakna, memudahkan analisis dan visualisasi data.

Atas ialah kandungan terperinci Bagaimana untuk Menghimpun dan Menggabungkan Berbilang Baris secara Dinamik dengan Berbilang Lajur dalam SQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!