SQL에서 열을 그룹화하고 여러 행을 여러 열이 있는 단일 행으로 결합하는 방법은 무엇입니까?

SQL Server에서 열을 그룹화하고 여러 행을 여러 열이 있는 단일 행으로 병합

경우에 따라 특정 열을 기준으로 데이터를 그룹화하고 관련 행의 여러 값을 여러 열이 포함된 단일 행으로 집계할 수 있습니다. 다음 상황을 예로 들어보겠습니다.

다음 열을 포함하는 Result라는 테이블이 있습니다.

• WorkOrder: 특정 주문에 대한 식별자
• TestType: 실행되는 테스트 유형
• 결과: 테스트 결과

결과 테이블의 데이터는 다음과 같습니다.

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

데이터를 다음 구조로 다시 포맷하려고 합니다.

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

여기서 과제는 결과를 TestType별로 그룹화하고 여러 결과 값을 Result1, Result2 등으로 라벨이 지정된 별도의 열로 결합하는 것입니다.

비동적 솔루션

결과 수가 고정된 경우 다음과 같은 간단한 접근 방식을 사용할 수 있습니다.

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

이 쿼리는 결과를 Result1, Result2, Result3의 세 열로 제한합니다. 그러나 결과의 양이 동적으로 증가하려면 더 복잡한 솔루션이 필요합니다.

동적 솔루션

불확실한 수의 결과를 처리하려면 필요한 열을 자동으로 생성하는 동적 SQL 쿼리를 사용할 수 있습니다.

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

이 쿼리는 모든 TestType에 대한 최대 결과 수를 기반으로 충분한 수의 결과 열을 생성하는 동적 SQL 문을 생성합니다. Tally라는 CTE(Common Table Expression)를 사용하여 Result 열의 열 번호를 동적으로 생성합니다.

위 내용은 SQL에서 열을 그룹화하고 여러 행을 여러 열이 있는 단일 행으로 결합하는 방법은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!