Tuesday, February 3, 2015
Pivot / Unpivot
create table Cart
(
Product varchar(32),
Color varchar(32),
Nr int
)
insert into Cart values ('car','blue',3)
insert into Cart values ('car','red',2)
insert into Cart values ('car','black',3)
insert into Cart values ('car','white',1)
insert into Cart values ('car','blue',1)
insert into Cart values ('carrot','blue',3)
insert into Cart values ('carrot','red',2)
insert into Cart values ('onion','black',3)
insert into Cart values ('onion','white',1)
insert into Cart values ('car','blue',1)
insert into Cart values ('beer','blue',3)
insert into Cart values ('car','red',2)
insert into Cart values ('car','black',3)
insert into Cart values ('beer','white',1)
insert into Cart values ('car','blue',1)
insert into Cart values ('carrot','blue',3)
insert into Cart values ('beer','red',2)
insert into Cart values ('onion','black',3)
insert into Cart values ('onion','white',1)
insert into Cart values ('car','blue',1)
insert into Cart values ('car','blue',1)
insert into Cart values ('beer','blue',3)
insert into Cart values ('car','red',2)
insert into Cart values ('beer','black',3)
insert into Cart values ('beer','white',1)
insert into Cart values ('car','blue',1)
insert into Cart values ('car','blue',3)
insert into Cart values ('beer','red',2)
select * into CartPivot from Cart
pivot
(
sum(Nr)
for Color in ([blue],[black],[white],[red])
) PivotTable
select * from CartPivot
select Product, Nr, details from CartPivot
Unpivot
(
Nr for details in ([blue],[black],[white],[red])
) as UnPvt
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment