Trouble with simple 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

SQL Server 2008 Forums

Transact-SQL (2008)

Trouble with simple PIVOT

Author

Topic

nietzky
Yak Posting Veteran

75 Posts

Posted - 2012-05-11 : 17:45:53

I am trying to create a pivot to show pct used per region on a specific date (once a week), I am getting O values when I try to do the pivot in SQL. What am I doing wrong? Thank you

SELECT WEEKNO, RUNDATE,
ISNULL([1], 0) AS EU1,
ISNULL([2], 0) AS EU2,
ISNULL([3], 0) AS EU3

FROM (
SELECT WEEKNO, RUNDATE, REGION, [% USED]

FROM dbo.HIST_TABLE

WHERE
TYPE='VOLUME'
AND CALENDAR_YEAR_NAME ='2012'
group by WEEKNO, RUNDATE, REGION,[% USED]

)as p
PIVOT (
[% USED] for REGION in ([1], [2], [3])
)as pvt
ORDER BY RUNDATE DESC

Current result (values are all zeros):
WEEKNO RUNDATE EU1 EU2 EU3
19 2012-05-11 00:00:00.0000000 0.000000000000000 0.000000000000000 0.000000000000000
18 2012-05-05 00:00:00.0000000 0.000000000000000 0.000000000000000 0.000000000000000
17 2012-04-27 00:00:00.0000000 0.000000000000000 0.000000000000000 0.000000000000000
16 2012-04-21 00:00:00.0000000 0.000000000000000 0.000000000000000 0.000000000000000
15 2012-04-14 00:00:00.0000000 0.000000000000000 0.000000000000000 0.000000000000000
14 2012-04-06 00:00:00.0000000 0.000000000000000 0.000000000000000 0.000000000000000
13 2012-03-31 00:00:00.0000000 0.000000000000000 0.000000000000000 0.000000000000000
12 2012-03-24 00:00:00.0000000 0.000000000000000 0.000000000000000 0.000000000000000
11 2012-03-17 00:00:00.0000000 0.000000000000000 0.000000000000000 0.000000000000000
10 2012-03-10 00:00:00.0000000 0.000000000000000 0.000000000000000 0.000000000000000
09 2012-03-03 00:00:00.0000000 0.000000000000000 0.000000000000000 0.000000000000000
08 2012-02-25 00:00:00.0000000 0.000000000000000 0.000000000000000 0.000000000000000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-11 : 23:40:05

you should be applying some aggregation over [% USED] field

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

nietzky
Yak Posting Veteran

75 Posts

Posted - 2012-05-12 : 01:39:37

I actually meant with AVG() I took it out for troubleshooting to see if it changes anything.... But the result is the same.
SELECT WEEKNO, RUNDATE,
ISNULL([1], 0) AS EU1,
ISNULL([2], 0) AS EU2,
ISNULL([3], 0) AS EU3

FROM (
SELECT WEEKNO, RUNDATE, REGION, [% USED]

FROM dbo.HIST_TABLE

WHERE
TYPE='VOLUME'
AND CALENDAR_YEAR_NAME ='2012'
group by WEEKNO, RUNDATE, REGION,[% USED]

)as p
PIVOT (
AVG([% USED]) for REGION in ([1], [2], [3])
)as pvt
ORDER BY RUNDATE DESC

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-12 : 05:56:43

remove ISnull function from all columns .Lets see what result u get .
If still issue not fic then please provide us sample data and desired output.

nietzky
Yak Posting Veteran

75 Posts

Posted - 2012-05-12 : 20:02:34

Yes removing the isnull statement fixed the problem. THANK YOU
WORKING SOLUTION:

SELECT WEEKNO,
RUNDATE,
EU1,
EU2,
EU3

FROM (
SELECT WEEKNO, RUNDATE, REGION, [% USED]

FROM dbo.HIST_TABLE

WHERE
TYPE='VOLUME'
AND CALENDAR_YEAR_NAME ='2012'
group by WEEKNO, RUNDATE, REGION,[% USED]

)as p
PIVOT (
avg([% USED]) for REGION in (EU1, EU2, EU3)
)as pvt
ORDER BY RUNDATE DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-12 : 20:06:03

quote:
Originally posted by nietzky

I actually meant with AVG() I took it out for troubleshooting to see if it changes anything.... But the result is the same.
SELECT WEEKNO, RUNDATE,
ISNULL([1], 0) AS EU1,
ISNULL([2], 0) AS EU2,
ISNULL([3], 0) AS EU3

FROM (
SELECT WEEKNO, RUNDATE, REGION, [% USED]

FROM dbo.HIST_TABLE

WHERE
TYPE='VOLUME'
AND CALENDAR_YEAR_NAME ='2012'
group by WEEKNO, RUNDATE, REGION,[% USED]

)as p
PIVOT (
AVG([% USED]) for REGION in ([1], [2], [3])
)as pvt
ORDER BY RUNDATE DESC

Are you sure your Region values are 1,2,3 etc in table?

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

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-13 : 02:26:34

quote:
Originally posted by nietzky

Yes removing the isnull statement fixed the problem. THANK YOU
WORKING SOLUTION:

SELECT WEEKNO,
RUNDATE,
EU1,
EU2,
EU3

FROM (
SELECT WEEKNO, RUNDATE, REGION, [% USED]

FROM dbo.HIST_TABLE

WHERE
TYPE='VOLUME'
AND CALENDAR_YEAR_NAME ='2012'
group by WEEKNO, RUNDATE, REGION,[% USED]

)as p
PIVOT (
avg([% USED]) for REGION in (EU1, EU2, EU3)
)as pvt
ORDER BY RUNDATE DESC

Welcome

Vijay is here to learn something from you guys.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-13 : 14:17:26

quote:
Originally posted by nietzky

Yes removing the isnull statement fixed the problem. THANK YOU
WORKING SOLUTION:

SELECT WEEKNO,
RUNDATE,
EU1,
EU2,
EU3

FROM (
SELECT WEEKNO, RUNDATE, REGION, [% USED]

FROM dbo.HIST_TABLE

WHERE
TYPE='VOLUME'
AND CALENDAR_YEAR_NAME ='2012'
group by WEEKNO, RUNDATE, REGION,[% USED]

)as p
PIVOT (
avg([% USED]) for REGION in (EU1, EU2, EU3)
)as pvt
ORDER BY RUNDATE DESC

so REGION values were Eu1,Eu2,etc
Your prev posted queries have them as 1,2,3...

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources