Using year function in Pivot - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using year function in Pivot

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-09-08 : 10:20:12

Hello there

i have the following example pivot script that works fine.

Select company_name, [2006-03-06], [2008-07-07]
from

(select company_name, status_date, [total_amount] from pivotTest ) AS sources

pivot
(
sum(total_amount) for status_date

in ([2006-03-06], [2008-07-07] )
)
as piv

is there a way i can use the year function as the columns.

eg

Select company_name, year(status_date)=2006, year(status_date)=2008

from

(select company_name, status_date, [total_amount] from pivotTest ) AS sources

pivot
(
sum(total_amount) for status_date

in (year(status_date)=2006, year(status_date)=2006 )
)
as piv

kind regards

Rob

MCTS / MCITP certified

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-09-08 : 10:29:25

its ok sorted it, thank you

all it did was select year(status_date) first

Select company_name, [2006], [2008]

from

(select company_name, year(status_date) as 'status date', [total_amount] from pivotTest ) AS sources

pivot
(
sum(total_amount) for status_date

in ([2006], [2008]
)
)
as piv

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources