T-SQL::동적 PIVOT 생성

원문 : http://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/

 

 

동적으로 PIVOT을 만들 수 있는 방법입니다.

아직도 case 문이 익숙하긴 하지만, 가끔은 오히려 더 복잡해지는 case 문 때문에 PIVOT을 사용해야 한다고 느껴질 때가 있습니다.

 

아직 PIVOT 익숙하지는 않은데 하다보니 컬럼 고정이 되서 불편한 점이 있는데 이 방법으로 사용하면 좋을 것 같네요.

 

PIVOT 은 행 형태의 반환을 열로 만들어 주는 것입니다.

 

아래로 테스트할 데이터를 생성 합니다.

CREATE TABLE dbo.Products
(
  ProductID INT PRIMARY KEY,
  Name      NVARCHAR(255) NOT NULL UNIQUE
  /* other columns */
);

INSERT dbo.Products VALUES
(1, N'foo'),
(2, N'bar'),
(3, N'kin');

CREATE TABLE dbo.OrderDetails
(
  OrderID INT,
  ProductID INT NOT NULL
    FOREIGN KEY REFERENCES dbo.Products(ProductID),
  Quantity INT
  /* other columns */
);

INSERT dbo.OrderDetails VALUES
(1, 1, 1),
(1, 2, 2),
(2, 1, 1),
(3, 3, 1);

 

SELECT p.Name, Quantity = SUM(o.Quantity)
  FROM dbo.Products AS p
  INNER JOIN dbo.OrderDetails AS o
  ON p.ProductID = o.ProductID
  GROUP BY p.Name;

 

결과는 아래 처럼 행으로 나 옵니다.  그러나 열로 표시하고 싶어요. 하면 PIVOT 처리 합니다.

 

 

 

SELECT p.[foo], p.[bar], p.[kin]
FROM
(
  SELECT p.Name, o.Quantity
   FROM dbo.Products AS p
   INNER JOIN dbo.OrderDetails AS o
   ON p.ProductID = o.ProductID
) AS j
PIVOT
(
  SUM(Quantity) FOR Name IN ([foo],[bar],[kin])
) AS p;

 

 

원하는 결과가 나왔습니다. ~

 

 

다른 데이터를 추가 해 보죠.

INSERT dbo.Products SELECT 4, N'blat';
INSERT dbo.OrderDetails SELECT 4,4,5;

 

그리고 나서 다시 윗 쿼리를 실행 해 보면 결과는 동일하게 3개의 데이터의 집계만 나타납니다.

당연한 결과 입니다. 지금 추가된 blat는 보이지 않습니다. PIVOT는 집계해서 보여줄 컬럼을 명시해야 하기 때문입니다.   쿼리를 그럼 고쳐야 하는데 좀 더 쉽게 동적으로 할 수 있게 아래는 도와 줍니다.

 

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);

SET @columns = N'';

SELECT @columns += N', p.' + QUOTENAME(Name)   -- += 는 컬럼을 합쳐서 한 문자로 만들어 주죠.
FROM (SELECT p.Name FROM dbo.Products AS p
  INNER JOIN dbo.OrderDetails AS o
  ON p.ProductID = o.ProductID
 GROUP BY p.Name) AS x;

print @columns

SET @sql = N'
 SELECT ' + STUFF(@columns, 1, 2, '') + '
 FROM
 (
   SELECT p.Name, o.Quantity
    FROM dbo.Products AS p
    INNER JOIN dbo.OrderDetails AS o
    ON p.ProductID = o.ProductID
 ) AS j
 PIVOT
 (
   SUM(Quantity) FOR Name IN ('
   + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
   + ')
 ) AS p;';

PRINT @sql;
EXEC sp_executesql @sql;

 

-- @sql의 문은 컬럼을 만들어서 PIVOT 를 생성해 줍니다.

 

/*결과)
 SELECT p.[foo], p.[bar], p.[kin], p.[blat]
 FROM
 (
   SELECT p.Name, o.Quantity
    FROM dbo.Products AS p
    INNER JOIN dbo.OrderDetails AS o
    ON p.ProductID = o.ProductID
 ) AS j
 PIVOT
 (
   SUM(Quantity) FOR Name IN ([foo],[bar],[kin],[blat])
 ) AS p;

*/

 

원하는 결과가 도출.