SQL 2005 Pivot Function Query - 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

SQL Server 2005 Forums

Transact-SQL (2005)

SQL 2005 Pivot Function Query

Author

Topic

stantheman21ph
Starting Member

2 Posts

Posted - 2008-12-21 : 22:42:46

Hi,

Anyone here knows how to simulate an excel pivot functionality through SQL code? Actually, I like to achieve the ff. if there is a way:
1.) On the data part, how do you show the results as % of column(in excel you just right click source field -> field settings -> choose sum -> click options -> choose "show data as % of column) through sql code?

2.) How do you implement the grand total for both the columns & rows of the pivot table through sql code?

I am pretty much new with the sql 2005 pivot function, what I know about this is just the basic stuff:

SELECT columns
FROM table
PIVOT
(
Aggregate Function(Measure Column)
FOR Pivot Column IN ([Pivot Column Values])
)
AS Alias

Any ideas on how to go about this or a workaround would be much appreciated.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2008-12-21 : 23:57:23

I give an Example For PIVOT:-

Create table Pivot1
(
Year smallint,
Quarter Tinyint,
Amount Decimal(18,3)
)

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

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

I Struggle For Excellence

stantheman21ph
Starting Member

2 Posts

Posted - 2008-12-22 : 20:00:36

Hi,

Here is a sample table and the pivot table I'm trying to achieve

Table:
Statcode Code1 Count
A BLA 3
A SLA 5
B CLB 10
C BDA 40
C SLA 30
D BDA 12
E BDA 8
F CLB 1
B CLB 7
A BDA 11

Pivot Table:( Achieved using excel functionality). This is a sample of what I want to simulate using SQL code.

Sum of Count Code1
Statcode BDA BLA CLB SLA Grand Total
A 15.49% 100.00% 0.00% 14.29% 14.96%
B 0.00% 0.00% 94.44% 0.00% 13.39%
C 56.34% 0.00% 0.00% 85.71% 55.12%
D 16.90% 0.00% 0.00% 0.00% 9.45%
E 11.27% 0.00% 0.00% 0.00% 6.30%
F 0.00% 0.00% 5.56% 0.00% 0.79%
GrandTotal 100.00% 100.00% 100.00% 100.00% 100.00%

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-22 : 20:12:02

On what basis did you calculate percentage? Can you explain further?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources