PIVOT 함수를 사용하여 SQL에서 열을 동적으로 생성하는 방법은 무엇입니까?

PIVOT 함수를 사용하여 SQL에서 열을 동적으로 생성

이 기사에서는 고객 보상 데이터베이스의 다양한 보상 유형을 나타내기 위해 SQL에서 열을 동적으로 생성하는 문제를 다룹니다. 보상 유형은 고정되어 있지 않으며 시간이 지남에 따라 변경될 수 있습니다. SQL PIVOT 함수를 사용하여 정적 접근 방식과 동적 접근 방식을 모두 보여주고 포괄적인 결과를 위한 전체 열 추가를 포함한 솔루션을 살펴보겠습니다.

문제:

고객, 보상, 보상 유형에 대한 데이터베이스 테이블이 있습니다. 보상 유형은 동적입니다. 추가하거나 제거할 수 있습니다. 목표는 각 보상 유형에 대한 열이 포함된 결과 집합을 생성하여 각 고객에 대한 해당 보상 유형의 개수를 표시하는 것입니다.

정적 솔루션(보상 유형 고정 개수):

보상 유형을 미리 알고 있는 경우 간단한 PIVOT 쿼리로 충분합니다.

<code class="language-sql">SELECT name, [Bronze], [Silver], [Gold], [Platinum], [AnotherOne]
FROM
(
  SELECT c.name, cr.description, r.typeid
  FROM customers c
  LEFT JOIN rewards r ON c.id = r.customerid
  LEFT JOIN customerrewards cr ON r.typeid = cr.typeid
) x
PIVOT
(
  COUNT(typeid)
  FOR description IN ([Bronze], [Silver], [Gold], [Platinum], [AnotherOne])
) p;</code>

이 쿼리는 잘 작동하지만 보상 유형("브론즈", "실버" 등)이 사전 정의된 경우에만 가능합니다.

동적 솔루션(보상 유형 수 알 수 없음):

동적인 보상 유형의 경우 동적 SQL이 필요합니다.

<code class="language-sql">DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(description)
                    FROM customerrewards
                    GROUP BY description, typeid
                    ORDER BY typeid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'');

SET @query = 'SELECT name,' + @cols + ' FROM 
             (
                SELECT c.name, cr.description, r.typeid
                FROM customers c
                LEFT JOIN rewards r ON c.id = r.customerid
                LEFT JOIN customerrewards cr ON r.typeid = cr.typeid
            ) x
            PIVOT 
            (
                COUNT(typeid)
                FOR description IN (' + @cols + ')
            ) p;';

EXECUTE(@query);</code>

이 코드는 customerrewards 테이블에서 열 목록을 작성하여 SQL 쿼리를 동적으로 구성합니다. 이를 통해 다양한 보상 유형에 적응할 수 있습니다.

총계 열 추가:

고객당 총 보상 수를 포함하려면 ROLLUP 기능을 사용할 수 있습니다(정적 버전과 동적 버전 모두 아래에 표시됨).

전체 정적 버전:

<code class="language-sql">SELECT name, SUM([Bronze]) Bronze, SUM([Silver]) Silver, 
  SUM([Gold]) Gold, SUM([Platinum]) Platinum, SUM([AnotherOne]) AnotherOne
FROM 
(
  SELECT name, [Bronze], [Silver], [Gold], [Platinum], [AnotherOne]
  FROM
  (
    SELECT c.name, cr.description, r.typeid
    FROM customers c
    LEFT JOIN rewards r ON c.id = r.customerid
    LEFT JOIN customerrewards cr ON r.typeid = cr.typeid
  ) x
  PIVOT
  (
    COUNT(typeid)
    FOR description IN ([Bronze], [Silver], [Gold], [Platinum], [AnotherOne])
  ) p
) x
GROUP BY name WITH ROLLUP;</code>

전체 동적 버전:

<code class="language-sql">DECLARE @cols AS NVARCHAR(MAX),
    @colsRollup AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(description) 
                    FROM customerrewards
                    GROUP BY description, typeid
                    ORDER BY typeid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

SELECT @colsRollup 
      = STUFF((SELECT ', SUM(' + QUOTENAME(description) + ') AS ' + QUOTENAME(description)
                    FROM customerrewards
                    GROUP BY description, typeid
                    ORDER BY typeid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');


SET @query 
          = 'SELECT name, ' + @colsRollup + '
             FROM
             (
                SELECT name,' + @cols + ' FROM 
                 (
                    SELECT c.name, cr.description, r.typeid
                    FROM customers c
                    LEFT JOIN rewards r ON c.id = r.customerid
                    LEFT JOIN customerrewards cr ON r.typeid = cr.typeid
                ) x
                PIVOT 
                (
                    COUNT(typeid)
                    FOR description IN (' + @cols + ')
                ) p 
              ) x1
              GROUP BY name WITH ROLLUP;';

EXECUTE(@query);</code>

이러한 동적 SQL 예제는 유용한 총 열을 포함하여 다양한 수의 보상 유형 열이 있는 보고서를 생성하기 위한 강력하고 유연한 솔루션을 제공합니다. 특정 데이터베이스 스키마와 일치하도록 테이블 및 열 이름을 조정해야 합니다.

위 내용은 PIVOT 함수를 사용하여 SQL에서 열을 동적으로 생성하는 방법은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!