Bagaimana untuk Menggabungkan Berbilang Baris SQL menjadi Satu dengan Lajur Dijana Secara Dinamik Berdasarkan Lajur Pengumpulan?

Jana lajur secara dinamik berdasarkan kumpulan lajur dan gabungkan berbilang baris data SQL

Soalan:

Bagaimana untuk mengumpulkan data mengikut lajur tertentu dan menggabungkan berbilang baris dengan nilai lajur yang sama ke dalam satu baris, menjana berbilang lajur pada masa yang sama? Khususnya, anda perlu mengumpulkan mengikut lajur "TestType" dan membahagi lajur "Result" kepada lajur berasingan untuk setiap nilai "TestType" yang sepadan.

Jawapan:

Ini boleh dicapai menggunakan Crosstab (PIVOT) dalam SQL Server. Walau bagaimanapun, jika bilangan lajur bagi setiap kumpulan adalah dinamik (iaitu mungkin berubah), penyelesaian yang lebih fleksibel diperlukan:

Penyelesaian dinamik menggunakan FOR XML PATH:

Buat CTE dengan nombor baris:

<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
)</code>

Bina pernyataan SQL dinamik:

<code class="language-sql">DECLARE @SQL nvarchar(MAX);
DECLARE @CRLF nvarchar(2) = CHAR(13) + CHAR(10); --回车换行符

-- 获取任何“TestType”的最大结果数
SELECT @MaxTally = MAX(C)
FROM (
    SELECT COUNT(*) AS C
    FROM dbo.Result
    GROUP BY WorkOrder,
             TestType
);

-- 创建具有最大行数的计数表
WITH Tally AS (
    SELECT TOP (@MaxTally) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N),
         (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)
)

-- 构建动态SQL语句
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 +
              -- 使用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;'</code>

Laksanakan pernyataan SQL dinamik:

<code class="language-sql">PRINT @SQL; -- 查看SQL语句

EXEC sys.sp_executesql @SQL;</code>

Penyelesaian ini akan menjana lajur secara dinamik berdasarkan bilangan keputusan maksimum untuk setiap "TestType", menghasilkan output yang diingini. Pembolehubah @CRLF telah ditambahkan pada kod untuk kebolehbacaan kod yang lebih baik dan keserasian merentas platform yang dipertingkatkan.

Atas ialah kandungan terperinci Bagaimana untuk Menggabungkan Berbilang Baris SQL menjadi Satu dengan Lajur Dijana Secara Dinamik Berdasarkan Lajur Pengumpulan?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!