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

PIVOT

Author

Topic

matta0990
Starting Member

44 Posts

Posted - 2011-08-26 : 08:40:41

Hi, how do i PIVOT a table so that all the results returned back display accround the top as headers instead of a list format in a coloum.

Thanx Mat

J Smith

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-26 : 09:29:24

If you have MS SQL Server 2005 or later:

http://msdn.microsoft.com/en-us/library/ms177410.aspx

You can also use Case expressions

Ex:


Select 
Sum(case when colA=SomeValue then 1 else 0 end) as 1stColHdr,
Sum(case when colB=SomeValue then 1 else 0 end) as 2ndColHdr, etc

From YourTable
Where SomeConditionsExist

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-26 : 09:31:06

can you show as an example?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

matta0990
Starting Member

44 Posts

Posted - 2011-08-26 : 09:42:32

ATTRIBUTE_Name

1 Al2O3
2 ARSENIC_TM
3 ASH_AA
4 ASH_AR
5 ASH_DA
6 ASH_FUSION_TEMP

So....... i want all these attribute names to display like

Al2O3 ARSENIC_TM ASH_AA ASH_AR ASH_DA ASH_FUSION_TEMP

J Smith

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-26 : 09:44:31

and what should come as data within them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

matta0990
Starting Member

44 Posts

Posted - 2011-08-26 : 09:55:16

err lets just say

Value

1 100
2 200
3 300
4 400
5 500
6 600

from same table

J Smith

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-26 : 10:09:30

[code]SELECT MAX(CASE WHEN Attribute_Name='Al2O3' THEN Value ELSE NULL END) AS Al2O3,
MAX(CASE WHEN Attribute_Name='ARSENIC_TM' THEN Value ELSE NULL END) AS ARSENIC_TM,
...
FROM Table
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-08-29 : 03:43:24

If the values are dynamic, use this
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources