sum in unpivot - 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)

sum in unpivot

Author

Topic

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-03 : 11:51:01

can I unpivot on an aggregate?

e.g

Select * from (
Select *
FROM TableA
)as t
UNPIVOT (columnx For TotalTypes In (

count(distinct([columnx])) as Type_Total,
SUM(Count(distinct( [columnx]))) OVER (PARTITION BY [Year]) as All_Type_Total

))
as tblPivot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-03 : 12:22:39

didnt get that. can you explain with some sample data what you're trying to achieve here?

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-04 : 05:06:15

I am trying to replace a UNION with an UNPIVOT. Currently I'm UNIONing 2 selects with 2 different Totals.
I did this for a different function with 12 UNIONs and improved the speed 10 times. In the other function the totals were worked out in a different function so I was able to unpivot them easily.
In this case I have aggregates I need to UNPIVOT. Is this not allowed, or have I made a syntax error?

--Union:
SELECT count(distinct([columnx])) as Total
FROM TableA
UNION
SELECT SUM(Count(distinct( [columnx]))) OVER (PARTITION BY [Year]) as Total
FROM TableA

--UNPIVOT attempt:
Select * from (
Select *
FROM TableA
)as t
UNPIVOT (columnx For TotalTypes In (
count(distinct([columnx])) as Type_Total,
SUM(Count(distinct( [columnx]))) OVER (PARTITION BY [Year]) as All_Type_Total

))
as tblPivot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources