Bagaimana untuk Menghimpunkan Lajur dan Menggabungkan Berbilang Baris ke dalam Baris Tunggal dengan Berbilang Lajur dalam SQL?

Kumpulkan lajur dan gabungkan berbilang baris menjadi satu baris dengan berbilang lajur dalam SQL Server

Dalam sesetengah kes, anda mungkin mahu mengumpulkan data mengikut lajur tertentu dan mengagregatkan berbilang nilai daripada baris yang berkaitan ke dalam satu baris dengan berbilang lajur. Mari kita ambil situasi berikut sebagai contoh:

Anda mempunyai jadual bernama Result yang mengandungi lajur berikut:

• WorkOrder: Pengecam untuk pesanan tertentu
• TestType: Jenis ujian yang dilaksanakan
• Keputusan: keputusan ujian

Data dalam jadual Hasil kelihatan seperti ini:

WorkOrder	TestType	Result
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

Anda ingin memformat semula data ke dalam struktur berikut:

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

Cabaran di sini adalah untuk mengumpulkan hasil mengikut TestType dan menggabungkan berbilang nilai Hasil ke dalam lajur yang berasingan, berlabel Result1, Result2, dsb.

Penyelesaian bukan dinamik

Untuk bilangan hasil yang tetap, anda boleh menggunakan pendekatan mudah:

<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 mengehadkan keputusan kepada tiga lajur, Result1, Result2 dan Result3. Walau bagaimanapun, untuk kuantiti hasil yang dinamik, penyelesaian yang lebih kompleks diperlukan.

Penyelesaian dinamik

Untuk mengendalikan bilangan hasil yang tidak pasti, anda boleh menggunakan pertanyaan SQL dinamik yang secara automatik mencipta lajur yang diperlukan:

<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' + @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 menjana pernyataan SQL dinamik yang mencipta bilangan lajur Hasil yang mencukupi berdasarkan bilangan keputusan maksimum untuk mana-mana TestType. Ia menggunakan CTE (Ungkapan Jadual Biasa) yang dipanggil Tally untuk menjana nombor lajur lajur Hasil secara dinamik.

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