Pivot sql table - weekly data - 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)

Pivot sql table - weekly data

Author

Topic

nietzky
Yak Posting Veteran

75 Posts

Posted - 2012-04-10 : 15:44:03

select VolPath, AVG([%Used])as AvgPercentUsed, YearAndWeek from dbo.someView
group by VolPath, YearAndWeek
order by YearandWeek

I have a SQL query that returns data in the following fashion:
VolPath AvgPercentUsed YearAndWeek
Vol1 42.33 2012-1
Vol2 13.36 2012-1
Vol3 78.9 2012-1
Vol4 54.01 2012-1
Vol1 43.33 2012-2
Vol2 15.36 2012-2
Vol3 78.9 2012-2
Vol4 54.01 2012-2
Vol1 44.33 2012-3
Vol2 19.36 2012-3
Vol3 78.9 2012-3
Vol4 54.01 2012-3

I need to re-write the query to display data in the following way.
1. Week data year-week should be displayed based on the last current value coming from [Yearandweek], I would like to avoid displaying all columns for the weeks for the whole year if the data is not there yet. Ex. if today is 15th week, display 15 weeks based on last import which is YearAndWeek
2. Current YearandWeek is formatted as varchar, I would like to incorporate '0' to allow me to sort it and graph teh data later. ex. 2012-01 , 2012-02 and so on....:

Desired result:
VolPath 2012-1 2012-2 2012-3 ...
Vol1 42.33 43.33 44.33
Vol2 13.36 15.36 19.36
Vol3 ...
Vol4 ...

I am just starting on this have some ideas with a temp table but would appreciate any input.
Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-10 : 15:53:45

[code]
declare @YrWkList varchar(1000),@sql varchar(4000)
select @YrWkList = stuff((select distinct '],['+ YearAndWeek from dbo.someView for xml path('')),1,2,'') + ']'

set @sql='select *
from
(
select VolPath, AVG([%Used])as AvgPercentUsed, YearAndWeek from dbo.someView
group by VolPath, YearAndWeek
)m
pivot (max(AvgPercentUsed) for YearAndWeek in (' + @YrWkList + '))p'

exec (@sql)
[/code]

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

nietzky
Yak Posting Veteran

75 Posts

Posted - 2012-04-10 : 16:11:52

visakh16 - THANK YOU.
1 more thing, how do I sort the values for the Year-Week, they are showing up in random order. I thin kI need to introduce 0 in front of week digit ex. instaed of 2012-1, perhaps 2012 -01 and so on for single digits?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-10 : 16:29:33

for sorting them in order use
STUFF(YearAndWeek,CHARINDEX('-',YearAndWeek),1,CASE WHEN LEN(YearAndWeek)=6 THEN '0' ELSE '' END) * 1

instead of simply YearAndWeek

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

nietzky
Yak Posting Veteran

75 Posts

Posted - 2012-04-10 : 16:57:20

I converted this to this query:
declare @YrWkList varchar(1000),@sql varchar(4000)
select @YrWkList = stuff((select distinct '],['+ STUFF(YearAndWeek,CHARINDEX('-',YearAndWeek),1,CASE WHEN LEN(YearAndWeek)=6 THEN '0' ELSE '' END) * 1

from dbo.some_view for xml path('')),1,2,'') + ']'

set @sql='select *
from
(
select VolPath, AVG([%Used])as AvgPercentUsed, WeekNo from dbo.some_view
group by VolPath, YearAndWeek
)m
pivot (max(AvgPercentUsed) for YearAndWeek in (' + @YrWkList + '))p'

exec (@sql)

Getting this error now:

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value '],[' to data type int.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources