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