Pivot Table Problems - 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 Table Problems

Author

Topic

juventus18
Starting Member

3 Posts

Posted - 2010-08-26 : 15:01:12

Hello,

Having some trouble making a pivot table. It is a dynamic pivot on dates. The user will submit the date range on which the table will be pivoted.

Here's the query I'm using to get the data for the pivot:


SELECT
    HW.Date, LMM.LMDesc, E.lastName + ', ' + E.firstName AS Name, SUM(LA.units)/SUM(HW.HoursWorked) AS UPH
FROM
    vHoursWorkedNSS AS HW 
LEFT OUTER JOIN
    tblEmployee AS E ON E.employeeID = HW.employeeID 
LEFT OUTER JOIN
    tblLineAssignment AS LA ON LA.lineAssignmentID = HW.lineAssignmentID 
LEFT OUTER JOIN
    tblLMMap AS LMM ON LMM.workTypeID = HW.workTypeID AND LMM.clientLocID = 18
WHERE
    (HW.Date BETWEEN '08/02/2010' AND '08/20/2010') AND (LA.clientLocID = 18)
GROUP BY 
    LMM.LMDesc, HW.Date, E.lastName + ', ' + E.firstName
ORDER BY 
    HW.Date, LMM.LMDesc, Name

Here is the result table (truncated):


DATE         LMDesc       Name        UPH
----------   ----------   --------    ------ 
08/02/2010   03 Pick      NAME1       915.892938
08/02/2010   03 Pick      NAME2       1014.556962
08/02/2010   05 Replenish NAME3       8.402154
08/03/2010   03 Pick      NAME1       923.589743
08/03/2010   03 Pick      NAME2       617.707

This is what the table should look like after pivot on date:


LMDesc        Name      08/02/2010      08/03/2010
-------       -------   -----------     ------------
03 Pick       Name1     915.892938      923.589743
03 Pick       Name2     1014.556962     617.707
05 Replenish  Name3     8.402154        NULL

But I can't figure out how to do the pivot :( Any assistance will be greatly appreciated.

Thanks for your time,
Sean

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-08-26 : 15:46:22

Try Jeff's code here:

http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx

juventus18
Starting Member

3 Posts

Posted - 2010-08-26 : 17:05:13

OK, just so I'm clear on how to do this, i am first going to copy the procedure code and run it in SQL Studio. Then use the procedure with my query, something like this:


exec CrossTab
'SELECT LastName, OrderDate FROM northwind..Employees Employees INNER JOIN northwind..Orders Orders ON (Employees.EmployeeID=Orders.EmployeeID) ',
'Year(OrderDate)',
'Count(LastName)[]',
'LastName'

However, when i try to create the procedure (copying and pasting exactly) i get this error:
Msg 156, Level 15, State 1, Procedure CrossTab, Line 14
Incorrect syntax near the keyword 'Pivot'.
Msg 156, Level 15, State 1, Procedure CrossTab, Line 19
Incorrect syntax near the keyword 'Pivot'.

any ideas what may be wrong? Am i just not doing it right? Sorry, i don't quite have the SQL chops to debug the procedure code :(

Thanks again,
Sean

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-08-26 : 17:17:06

Um, sorry about that.

Do a find/replace on "pivot" and change it to "_pivot" or "_pivot_", and re-create or alter the procedure with these changes.

At the time the article was written, it was intended for SQL Server 2000 and PIVOT was not a keyword then. It is a keyword in SQL 2005 and higher.

juventus18
Starting Member

3 Posts

Posted - 2010-08-26 : 17:56:29

EXCELLENT! That was exactly what I needed! Thanks so much robvolk!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-27 : 04:17:49

Use PIVOT operator for versions starting from 2005
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources