/**********************************************************************************************
-- Title : [2k5] PIVIT, UNPIVOT 셈플 예제
-- Reference : mcpworld.com
-- Key word : 피벗 언피벗 pivot unpivot
**********************************************************************************************/
use tempdb;
go
drop table products, properties;
go
/*
pivot 기능은 행을 컬럼명으로 변환한다. 실제로 새로운 테이블을 합성한다
테이블에 컬럼들을 추가함으로써 테이블을 확장한다.
pivot은 1대다 테이블을 rotate할 수 있다
*/
create table products (id int, name varchar(15));
go
insert products values (1, 'paint');
insert products values (2, 'bolts');
insert products values (3, 'paint');
go
create table properties (pid int, name varchar(15), value sql_variant);
go
insert properties values (1, 'color', 'red');
insert properties values (1, 'type', 'latex');
insert properties values (1, 'amount', '2 liters');
insert properties values (2, 'pitch', '12-3');
insert properties values (2, 'diameter', '1 in');
go
select *
from properties;
go
/*
pid name value
--- --------- --------
1 color red
1 type latex
1 amount 2 liters
2 pitch 12-3
2 diameter 1 in
*/
select *
from properties
pivot (max(value) for [name] in ([color], [type], [amount])) as p
where pid = 1;
go
/*
pid color type amount
--- ------ ------ --------
1 red latex 2 liters
*/
select *
from properties
pivot (max(value) for [name] in ([color], [type])) as p
where pid = 1;
go
/*
pid color type
--- ------ ------
1 red latex
*/
select *
from properties
pivot (min(value) for [name] in ([color], [type])) as p
where pid = 1;
go
/*
pid color type
--- ------ ------
1 red latex
*/
create table hardware_sales
(
hardware_salesid int identity primary key clustered,
product nvarchar(30),
quantity int,
quarter int
);
go
insert into hardware_sales values ('swish', 15, 1)
insert into hardware_sales values ('swish', 5, 2)
insert into hardware_sales values ('attach-it', 36, 1)
insert into hardware_sales values ('attach-it', 32, 2)
insert into hardware_sales values ('attach-it', 41, 3)
insert into hardware_sales values ('attach-it', 36, 4)
insert into hardware_sales values ('paint', 12, 3)
insert into hardware_sales values ('paint', 35, 4)
insert into hardware_sales values ('rug', 15, 1)
insert into hardware_sales values ('rug', 28, 2)
insert into hardware_sales values ('rug', 34, 3)
insert into hardware_sales values ('rug', 40, 4)
go
select * from hardware_sales;
with pivotcte as
( select product, quantity, quarter
from hardware_sales
)
select
product, [1] as 'q1', [2] as 'q2', [3] as 'q3', [4] as 'q4'
from [pivotcte]
pivot (sum(quantity) for quarter in ([1], [2], [3], [4])) as pvt;
go
/*
--Create the table and insert values as portrayed in the above example.
-- 컬럼명을 행으로 전환한다.
*/
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int)
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4)
INSERT INTO pvt VALUES (2,4,1,5,5,5)
INSERT INTO pvt VALUES (3,4,3,5,4,4)
INSERT INTO pvt VALUES (4,4,2,5,5,4)
INSERT INTO pvt VALUES (5,5,1,5,5,5)
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt
GO