Bagaimana untuk Menghimpunkan mengikut Lajur dan Menggabungkan Berbilang Baris menjadi Satu dengan Bilangan Dinamik Lajur dalam SQL?

Penghimpunan lajur dinamik SQL Server dan penggabungan berbilang baris

Soalan:

Dalam jadual pangkalan data, terdapat berbilang baris keputusan untuk setiap gabungan TestType dan WorkOrder. Matlamatnya adalah untuk mengumpulkan mengikut TestType dan menggabungkan hasil ke dalam satu baris, dengan setiap nilai Hasil dalam lajur yang berasingan.

Penyelesaian:

Penyelesaian dinamik diperlukan untuk mengendalikan bilangan hasil yang tidak pasti. Skrip berikut boleh mengendalikan sehingga 100 hasil. Lebih daripada 100 keputusan, perlu menambah lebih banyak CROSS JOIN dalam CTE Tally.

<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) --100 行，更多行需要添加更多 N
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>

Data sampel:

<code class="language-sql">CREATE TABLE Result(WorkOrder varchar(10), TestType varchar(20), Result decimal(10,2));
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>

Keluaran yang dijangkakan:

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

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