Microsoft SQL Server
피벗 / 언 피봇

통사론

• SELECT <non-pivoted column> ,
  [첫 번째 피벗 된 열] AS <column name> ,
  [두 번째 피벗 된 열] AS <column name> ,
  ...
  [마지막 피벗 닝 된 열] AS <column name>
  에서
  ( <SELECT query that produces the data> )
  AS <alias for the source query>
  피벗
  (
  <aggregation function> ( <column being aggregated> )
  에 대한
  [ <column that contains the values that will become column headers> ]
  IN ([첫 번째 피벗 된 열], [두 번째 피벗 된 열],
  ... [마지막 피벗 된 열])
  ) AS <alias for the pivot table> <optional ORDER BY clause> ;

비고

단순 피벗 - 정적 열

Example Database의 Item Sales Table 을 사용하여 각 제품의 판매 수량을 계산하여 보여주십시오.

이 작업은 그룹을 사용하여 쉽게 수행 할 수 있지만 각 제품 ID에 대해 열이있는 방식으로 결과 테이블을 '회전'한다고 가정 할 수 있습니다.

SELECT [100], [145]
  FROM (SELECT ItemId , Quantity
          FROM #ItemSalesTable
       ) AS pivotIntermediate
 PIVOT (   SUM(Quantity)
           FOR ItemId IN ([100], [145])
       ) AS pivotTable

우리의 '새로운'열은 소스 테이블의 숫자이기 때문에 대괄호 [] 사용해야합니다 []

이것은 우리에게 다음과 같은 결과를 줄 것이다.

단순 PIVOT 및 UNPIVOT (T-SQL)

다음은 평일 당 각 항목의 평균 항목 가격을 보여주는 간단한 예입니다.

먼저 모든 품목의 가격을 매일 기록하는 테이블이 있다고 가정합니다.

CREATE TABLE tbl_stock(item NVARCHAR(10), weekday NVARCHAR(10), price INT);

INSERT INTO tbl_stock VALUES 
('Item1', 'Mon', 110), ('Item2', 'Mon', 230), ('Item3', 'Mon', 150), 
('Item1', 'Tue', 115), ('Item2', 'Tue', 231), ('Item3', 'Tue', 162), 
('Item1', 'Wed', 110), ('Item2', 'Wed', 240), ('Item3', 'Wed', 162), 
('Item1', 'Thu', 109), ('Item2', 'Thu', 228), ('Item3', 'Thu', 145), 
('Item1', 'Fri', 120), ('Item2', 'Fri', 210), ('Item3', 'Fri', 125),
('Item1', 'Mon', 122), ('Item2', 'Mon', 225), ('Item3', 'Mon', 140),
('Item1', 'Tue', 110), ('Item2', 'Tue', 235), ('Item3', 'Tue', 154),
('Item1', 'Wed', 125), ('Item2', 'Wed', 220), ('Item3', 'Wed', 142);

표는 다음과 같이 보입니다.

+========+=========+=======+
|   item | weekday | price |
+========+=========+=======+
|  Item1 |    Mon  |   110 |
+--------+---------+-------+
|  Item2 |    Mon  |   230 |
+--------+---------+-------+
|  Item3 |    Mon  |   150 |
+--------+---------+-------+
|  Item1 |    Tue  |   115 |
+--------+---------+-------+
|  Item2 |    Tue  |   231 |
+--------+---------+-------+
|  Item3 |    Tue  |   162 |
+--------+---------+-------+
|          . . .           |
+--------+---------+-------+
|  Item2 |    Wed  |   220 |
+--------+---------+-------+
|  Item3 |    Wed  |   142 |
+--------+---------+-------+

각 요일에 대해 항목 당 평균 가격을 찾기위한 집계를 수행하기 위해 관계 연산자 PIVOT 을 사용하여 테이블 값 표현식의 weekday 열을 아래와 같이 집계 된 행 값으로 회전합니다.

SELECT * FROM tbl_stock
PIVOT ( 
    AVG(price) FOR weekday IN ([Mon], [Tue], [Wed], [Thu], [Fri])
) pvt;

결과:

+--------+------+------+------+------+------+
|  item  |  Mon |  Tue |  Wed |  Thu |  Fri |
+--------+------+------+------+------+------+
|  Item1 |  116 |  112 |  117 |  109 |  120 |
|  Item2 |  227 |  233 |  230 |  228 |  210 |
|  Item3 |  145 |  158 |  152 |  145 |  125 |
+--------+------+------+------+------+------+

마지막으로, PIVOT 의 역 연산을 수행하기 위해 관계 연산자 UNPIVOT 을 사용하여 열을 다음과 같이 행으로 순환시킬 수 있습니다.

SELECT * FROM tbl_stock
PIVOT ( 
    AVG(price) FOR weekday IN ([Mon], [Tue], [Wed], [Thu], [Fri])
) pvt
UNPIVOT ( 
    price FOR weekday IN ([Mon], [Tue], [Wed], [Thu], [Fri])
) unpvt;

결과:

+=======+========+=========+
|  item |  price | weekday |
+=======+========+=========+
| Item1 |    116 |     Mon |
+-------+--------+---------+
| Item1 |    112 |     Tue |
+-------+--------+---------+
| Item1 |    117 |     Wed |
+-------+--------+---------+
| Item1 |    109 |     Thu |
+-------+--------+---------+
| Item1 |    120 |     Fri |
+-------+--------+---------+
| Item2 |    227 |     Mon |
+-------+--------+---------+
| Item2 |    233 |     Tue |
+-------+--------+---------+
| Item2 |    230 |     Wed |
+-------+--------+---------+
| Item2 |    228 |     Thu |
+-------+--------+---------+
| Item2 |    210 |     Fri |
+-------+--------+---------+
| Item3 |    145 |     Mon |
+-------+--------+---------+
| Item3 |    158 |     Tue |
+-------+--------+---------+
| Item3 |    152 |     Wed |
+-------+--------+---------+
| Item3 |    145 |     Thu |
+-------+--------+---------+
| Item3 |    125 |     Fri |
+-------+--------+---------+

동적 PIVOT

PIVOT 쿼리의 한 가지 문제점은 열로 보는 경우 IN 선택 내부의 모든 값을 지정해야한다는 것입니다. 이 문제를 피하는 빠른 방법은 PIVOT 동적으로 만드는 동적 IN 선택을 만드는 것입니다.

데모를 위해 우리는 Bookstore 의 데이터베이스에서 Books 를 사용할 것입니다. 우리는 테이블이 상당히 정규화되어 있고 다음 열이 있다고 가정합니다.

Table: Books
-----------------------------
BookId (Primary Key Column)
Name
Language
NumberOfPages
EditionNumber
YearOfPrint
YearBoughtIntoStore
ISBN
AuthorName
Price
NumberOfUnitsSold

테이블 작성 스크립트는 다음과 같습니다.

CREATE TABLE [dbo].[BookList](
      [BookId] [int] NOT NULL,
      [Name] [nvarchar](100) NULL,
      [Language] [nvarchar](100) NULL,
      [NumberOfPages] [int] NULL,
      [EditionNumber] [nvarchar](10) NULL,
      [YearOfPrint] [int] NULL,
      [YearBoughtIntoStore] [int] NULL,
[NumberOfBooks] [int] NULL,
[ISBN] [nvarchar](30) NULL,
      [AuthorName] [nvarchar](200) NULL,
      [Price] [money] NULL,
      [NumberOfUnitsSold] [int] NULL,
 CONSTRAINT [PK_BookList] PRIMARY KEY CLUSTERED
(
      [BookId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

이제 데이터베이스를 쿼리하고 영어, 러시아어, 독일어, 힌디어, 라틴어로 책을 사서 매년 작은 서적 형식으로 출력해야한다면 이처럼 PIVOT 쿼리를 사용할 수 있습니다

SELECT * FROM
  (
   SELECT YearBoughtIntoStore AS [Year Bought],[Language], NumberOfBooks
   FROM BookList
  ) sourceData 
 PIVOT
  (
  SUM(NumberOfBooks)
  FOR [Language] IN (English, Russian, German, Hindi, Latin)
  ) pivotrReport

특수한 경우는 언어의 전체 목록을 가지고 있지 않기 때문에 아래와 같이 동적 SQL을 사용합니다.

DECLARE @query VARCHAR(4000)
DECLARE @languages VARCHAR(2000)
SELECT @languages =
        STUFF((SELECT DISTINCT '],['+LTRIM([Language])FROM [dbo].[BookList]
        ORDER BY '],['+LTRIM([Language]) FOR XML PATH('') ),1,2,'') + ']'
SET @query=
'SELECT * FROM
  (SELECT YearBoughtIntoStore AS [Year Bought],[Language],NumberOfBooks
   FROM BookList) sourceData
PIVOT(SUM(NumberOfBooks)FOR [Language] IN ('+ @languages +')) pivotrReport' EXECUTE(@query)