How to Group Columns and Combine Multiple Rows into a Single Row with Multiple Columns in SQL?

Group columns and merge multiple rows into single row with multiple columns in SQL Server

In some cases, you may want to group data by specific columns and aggregate multiple values ​​from related rows into a single row with multiple columns. Let’s take the following situation as an example:

You have a table called Result that contains the following columns:

• WorkOrder: Identifier for a specific order
• TestType: Type of test executed
• Result: test result

The data in the Result table looks like this:

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

You wish to reformat the data into the following structure:

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

The challenge here is to group the results by TestType and combine the multiple Result values ​​into separate columns, labeled Result1, Result2, etc.

Non-dynamic solution

For a fixed number of results, you can use a straightforward approach:

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;

This query limits the results to three columns, Result1, Result2, and Result3. However, for dynamic quantities of results, a more complex solution is required.

Dynamic solutions

To handle an uncertain number of results, you can use a dynamic SQL query that automatically creates the necessary columns:

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;

This query generates a dynamic SQL statement that creates a sufficient number of Result columns based on the maximum number of results for any TestType. It uses a CTE (Common Table Expression) called Tally to dynamically generate the column number of the Result column.

The above is the detailed content of How to Group Columns and Combine Multiple Rows into a Single Row with Multiple Columns in SQL?. For more information, please follow other related articles on the PHP Chinese website!