A pivot question I think - 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 2012 Forums

Transact-SQL (2012)

A pivot question I think

Author

Topic

loydall
Starting Member

33 Posts

Posted - 2013-11-21 : 09:03:05

Hi - I have 2 tables.

tblDates is 2 columns - id, weekStarting

where id is the PK and weekStarting is a date field.

The data is just a list of dates.

My second table, tblPlan is like this:

id, fkProject, fkDate, dateValue

where id is the PK, fkProject links to a project table, fkDate links to tblDates and dateValue is a value for the amount of resource available for that week.

So, example data:

tblDates:

id | weekStarting
--------------------
1 | 4/11/2013
2 | 11/11/2013
3 | 18/11/2013
4 | 25/11/2013
5 | 2/12/2013

And tblPlan:

id | fkProject | fkDate | dateValue
-------------------------------------
1 | 1 | 1 | 45
2 | 1 | 3 | 33
3 | 1 | 4 | 12
4 | 2 | 1 | 89
5 | 2 | 5 | 34

But - I want to query the data so I the dates in tblDates become the column headings and then for each project we get dateValue plotted against date in a grid like this:

4/11/2013 | 11/11/2013 | 18/11/2013 | 25/11/2013 | 2/12/2013
-------------------------------------------------------------
45 | | 33 | 12 |
89 | | | | 34

So - see, the rows represent a project and display the allocation for each week - but note how some weeks are blank - I need to display a full grid generated from all the dates in tblDates even if there's no allocation in any project for that date.

I'm probably over complicating things there but my question is - how would I query those 2 tables to generate that table of results?

Come to think of it - I'd also need a fkProject column on the grid as well - as the first column, just so I knew which project each row represented.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-21 : 09:34:38

yep you need pivot like below


DECLARE @DateList varchar(max),@SQL varchar(max)

SET @DateList = STUFF((SELECT ',[' + WeekStarting + ']' FROM tblDates ORDER BY id FOR XML PATH('')),1,1,'')

SET @SQL= 'SELECT *
FROM
(
SELECT weekStarting,dateValue
FROM tblPlan p
LEFT JOIN tblDates d
On d.id = p.fkDate
)m
PIVOT(SUM(dateValue) FOR weekStarting IN (' + @DateList + '))n'

EXEC(@SQL)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

loydall
Starting Member

33 Posts

Posted - 2013-11-21 : 10:09:41

Thanks - that's nearly there I think but it's only returning 1 row and it looks like it's adding (SUM) the values for those days into that row. I need 1 row per fkProject - so I can see the the dateValue field for each date for each project.

The dateValue field is a little misleading - it's actually a varchar field - it can be a numeric value or N/A or whatever.

Ideally I'd like another, first column on the results that showed the fkProject.. So

fkProject | Nov42013 | Nov112013 | Nov182013 | Nov252013 | Dec22013
-------------------------------------------------------------------------
1 | 23 | N/A | | | | 33 |
2 | | tbc | 34 | | | |

Something like that.

loydall
Starting Member

33 Posts

Posted - 2013-11-21 : 10:28:05

ok - ignore that last question, think I got it figured.

Just needed to add the fkProject field to the select and then change the join to a right outer join to include dates that didn't have a value for any of the prjects.

Ended up with this:

DECLARE @DateList varchar(max),@SQL varchar(max)

SET @DateList = STUFF((SELECT ',[' + WeekStarting + ']' FROM tblDates ORDER BY id FOR XML PATH('')),1,1,'')

SET @SQL= 'SELECT *
FROM
(
SELECT weekStarting,dateValue, fkProject
FROM tblPlan p
right outer join tblDates d
On d.id = p.fkDate
where fkProject IS NOT NULL
)m
PIVOT(SUM(dateValue) FOR weekStarting IN (' + @DateList + '))n'

EXEC(@SQL)

Thanks for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-22 : 05:59:19

yep..thats the way to go. you didnt have projectid displayed in last select so i didnt realize it should be included

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources