SQL Server, PIVOT sample
/*
Let's create basic tables for the pivot query and fill lthem
with some test values
*/
drop table shop
create table shop( id int, shop_address varchar(255))
go
insert into shop(id , shop_address) values(1 , '5th Avenue N21')
insert into shop(id , shop_address) values(2 , 'Champs-Élysées N12')
go
create table docs(id int identity(1,1) , shop_id int , doc_date datetime)
go
insert into docs(shop_id , doc_date) values(1,'20101201')
insert into docs(shop_id , doc_date) values(1,'20101101')
insert into docs(shop_id , doc_date) values(2,'20101201')
insert into docs(shop_id , doc_date) values(2,'20101101')
insert into docs(shop_id , doc_date) values(2,'20101102')
go
/*
Let's create a query delivering all documents for the last
two months of 2010
*/
select shop_address , [11] , [12]
from
(select
s.shop_address ,
d.shop_id as ShopId ,
d.shop_id ,
MONTH(doc_date) as m
from docs as d
inner join shop as s on s.id = d.shop_id )
UP
PIVOT
(count(shop_id) for m in ( [11] , [12]) ) as pvt
order by ShopId
The result should look like this
Let's create basic tables for the pivot query and fill lthem
with some test values
*/
drop table shop
create table shop( id int, shop_address varchar(255))
go
insert into shop(id , shop_address) values(1 , '5th Avenue N21')
insert into shop(id , shop_address) values(2 , 'Champs-Élysées N12')
go
create table docs(id int identity(1,1) , shop_id int , doc_date datetime)
go
insert into docs(shop_id , doc_date) values(1,'20101201')
insert into docs(shop_id , doc_date) values(1,'20101101')
insert into docs(shop_id , doc_date) values(2,'20101201')
insert into docs(shop_id , doc_date) values(2,'20101101')
insert into docs(shop_id , doc_date) values(2,'20101102')
go
/*
Let's create a query delivering all documents for the last
two months of 2010
*/
select shop_address , [11] , [12]
from
(select
s.shop_address ,
d.shop_id as ShopId ,
d.shop_id ,
MONTH(doc_date) as m
from docs as d
inner join shop as s on s.id = d.shop_id )
UP
PIVOT
(count(shop_id) for m in ( [11] , [12]) ) as pvt
order by ShopId
The result should look like this
| shop_address | 11 | 12 | |
| 5th Avenue N21 | 1 | 1 | |
| Champs-Élysées N12 | 2 | 1 |
Comments
Write the first comment
Leave a trace
Post being viewed right now
