home
Pivot statement
Author Nigel Rivett
declare @a table (Company varchar(20), dte datetime, value int)
insert @a select 'Company01', '20030101', '100'
insert @a select 'Company02', '20030101', '200'
insert @a select 'Company03', '20030101', '300'
insert @a select 'Company01', '20030102', '400'
insert @a select 'Company02', '20030102', '500'
insert @a select 'Company03', '20030102', '600'
insert @a select 'Company01', '20030103', '700'
insert @a select 'Company02', '20030103', '800'
insert @a select 'Company03', '20030103', '900'
-- pivot individual entries
select 'sumvalue' as valuecol
,Company01, Company02, Company03
from (select company, value from @a where dte = '20030101') a
pivot
(
sum(value)
for Company in (Company01, Company02, Company03)
) as b
Result
valuecol Company01 Company02 Company03
-------- ----------- ----------- -----------
sumvalue 100 200 300
-- pivot and sum values
select 'sumvalue' as valuecol
,Company01, Company02, Company03
from (select company, value from @a) a
pivot
(
sum(value)
for Company in (Company01, Company02, Company03)
) as b
Result
valuecol Company01 Company02 Company03
-------- ----------- ----------- -----------
sumvalue 1200 1500 1800
-- Pivot and sum by date
select dte
,Company01, Company02, Company03
from (select company, value, dte from @a) a
pivot
(
sum(value)
for Company in (Company01, Company02, Company03)
) as b
Result
dte Company01 Company02 Company03
----------------------- ----------- ----------- -----------
2003-01-01 00:00:00.000 100 200 300
2003-01-02 00:00:00.000 400 500 600
2003-01-03 00:00:00.000 700 800 900
home