To get the column list horizontally

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

SQL Server 2005 Forums

Transact-SQL (2005)

To get the column list horizontally

Author

Topic

mrm23
Posting Yak Master

198 Posts

Posted - 2009-03-19 : 00:53:25

Hi,
i have a list of training names which i want to display horizontally for reporting purpose.
can i do it with PIVOT? can anyone explain where will PIVOT be appropriate and why?

the data is:
training_name
A
B
C
D
E
F
G
H

i want it as :
A|B|C|D|E|F|G|H - columns of my report

thanks in advance....

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-19 : 02:23:46

This is My table data
Year Quarter Amount
1990 1 1.100
1990 2 1.200
1990 3 1.300
1990 4 1.400
1991 1 2.100
1991 2 2.200
1991 3 2.300
1991 4 2.400
1991 4 2.400
1990 4 2.400

with out using Pivot:-

Select Year,
sum( case Quarter when 1 then Amount else 0 end) as Q1,
sum( case Quarter when 2 then Amount else 0 end) as Q2,
sum( case Quarter when 3 then Amount else 0 end) as Q3,
sum( case Quarter when 4 then Amount else 0 end) as Q4
from Pivot1
Group By Year

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-19 : 02:24:18

Using Pivot:-

Select Year,[1] as Q1,[2] as Q2,[3] as Q3,[4] as Q4
from ( select * from Pivot1) as P
pivot( SUM(Amount) for Quarter IN ( [1],[2],[3],[4] ) ) as pc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-19 : 12:50:27

quote:
Originally posted by mrm23

Hi,
i have a list of training names which i want to display horizontally for reporting purpose.
can i do it with PIVOT? can anyone explain where will PIVOT be appropriate and why?

the data is:
training_name
A
B
C
D
E
F
G
H

i want it as :
A|B|C|D|E|F|G|H - columns of my report

thanks in advance....

pivot should have a field for aggregation. do you've any other info which you need to aggregate upon based on row values?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources