UNPIVOT on 2 columns

[ajhess]

I have a payroll table that holds dollars by month and hours by month as columns, like so...
Employee | Hours1 | Hours2 | Hours3 | ... | Dollars1 | Dollars2 | Dollars3 |
Andrew | 40 | 40 | 45 | ... | 1000 | 1000 | 1500 |

I would like to unpivot this data to display like this...
Employee | Month | Hours | Dollars
Andrew | 1 | 40 | 1000
Andrew | 2 | 40 | 1000
Andrew | 3 | 45 | 1500

I can get the UNPIVOT command working on either the Hours or Dollars columns separately, but cannot figure out how to UNPIVOT twice and combine the results together. Any help would be appreciated, my code is below...

SELECT CostCenter, EmployeeID, DataSetID, PayType, SummaryPayType, JobClass, Description, Mnth, MonthHours, MonthDollars
FROM 
	(SELECT "T_EMPLOYEE_RESULTS"."CostCenter", "T_EMPLOYEE_RESULTS"."EmployeeID", "T_EMPLOYEE_RESULTS"."DataSetID", 
		"T_PAY_TYPES"."PayType", "T_PAY_TYPES"."SummaryPayType", "T_EMPLOYEE_RESULTS_MONTH_DETAIL"."Hours01" AS "Jul", 
		"T_EMPLOYEE_RESULTS_MONTH_DETAIL"."Hours02" AS "Aug", "T_EMPLOYEE_RESULTS_MONTH_DETAIL"."Hours03" AS "Sept", 
		"T_EMPLOYEE_RESULTS_MONTH_DETAIL"."Hours04" AS "Oct", "T_EMPLOYEE_RESULTS_MONTH_DETAIL"."Hours05" AS "Nov", 
		"T_EMPLOYEE_RESULTS_MONTH_DETAIL"."Hours06" AS "Dec", "T_EMPLOYEE_RESULTS_MONTH_DETAIL"."Hours07" AS "Jan", 
		"T_EMPLOYEE_RESULTS_MONTH_DETAIL"."Hours08" AS "Feb", "T_EMPLOYEE_RESULTS_MONTH_DETAIL"."Hours09" AS "Mar", 
		"T_EMPLOYEE_RESULTS_MONTH_DETAIL"."Hours10" AS "Apr", "T_EMPLOYEE_RESULTS_MONTH_DETAIL"."Hours11" AS "May", 
		"T_EMPLOYEE_RESULTS_MONTH_DETAIL"."Hours12" AS "Jun", "T_EMPLOYEE_RESULTS_MONTH_DETAIL"."Dollars01" AS "Jul", 
		"T_EMPLOYEE_RESULTS_MONTH_DETAIL"."Dollars02" AS "Aug", "T_EMPLOYEE_RESULTS_MONTH_DETAIL"."Dollars03" AS "Sept", 
		"T_EMPLOYEE_RESULTS_MONTH_DETAIL"."Dollars04" AS "Oct", "T_EMPLOYEE_RESULTS_MONTH_DETAIL"."Dollars05" AS "Nov", 
		"T_EMPLOYEE_RESULTS_MONTH_DETAIL"."Dollars06" AS "Dec", "T_EMPLOYEE_RESULTS_MONTH_DETAIL"."Dollars07" AS "Jan", 
		"T_EMPLOYEE_RESULTS_MONTH_DETAIL"."Dollars08" AS "Feb", "T_EMPLOYEE_RESULTS_MONTH_DETAIL"."Dollars09" AS "Mar", 
		"T_EMPLOYEE_RESULTS_MONTH_DETAIL"."Dollars10" AS "Apr", "T_EMPLOYEE_RESULTS_MONTH_DETAIL"."Dollars11" AS "May", 
		"T_EMPLOYEE_RESULTS_MONTH_DETAIL"."Dollars12" AS "Jun", "T_EMPLOYEE_RESULTS"."FacilityID", "T_EMPLOYEE_RESULTS"."JobClass", 
		"T_JOB_CLASSES"."Description"
 
	FROM   (("LG_SYSTEM"."dbo"."T_EMPLOYEE_RESULTS_MONTH_DETAIL" "T_EMPLOYEE_RESULTS_MONTH_DETAIL" INNER JOIN 
		 "LG_SYSTEM"."dbo"."T_EMPLOYEE_RESULTS" "T_EMPLOYEE_RESULTS" ON "T_EMPLOYEE_RESULTS_MONTH_DETAIL"."DetailKey"="T_EMPLOYEE_RESULTS"."DetailKey") INNER JOIN 
		 "LG_SYSTEM"."dbo"."T_PAY_TYPES" "T_PAY_TYPES" ON (("T_EMPLOYEE_RESULTS"."PayType"="T_PAY_TYPES"."PayType") AND ("T_EMPLOYEE_RESULTS"."DataSetID"="T_PAY_TYPES"."DataSetID")) 
			AND ("T_EMPLOYEE_RESULTS"."FacilityID"="T_PAY_TYPES"."FacilityID")) LEFT OUTER JOIN "LG_SYSTEM"."dbo"."T_JOB_CLASSES" "T_JOB_CLASSES" 
			ON (("T_EMPLOYEE_RESULTS"."FacilityID"="T_JOB_CLASSES"."FacilityID") AND ("T_EMPLOYEE_RESULTS"."JobClass"="T_JOB_CLASSES"."JobClass")) AND 
			("T_EMPLOYEE_RESULTS"."DataSetID"="T_JOB_CLASSES"."DataSetID")
		)P

UNPIVOT
(MonthHours FOR Mnth IN (Jul, Aug, Sept, Oct, Nov, Dec, Jan, Feb, Mar, Apr, May, Jun)
  )AS unpivoted1
UNPIVOT
(MonthDollars FOR Mnth IN (Jul, Aug, Sept, Oct, Nov, Dec, Jan, Feb, Mar, Apr, May, Jun)
  )AS unpivoted2

 

[gk53]

why not just simple
Hours = isnull(Hours1,0) + isnull(Hours2,0) + isnull(Hours3,0) + isnull(Hours4,0) + isnull(Hours5,0) + isnull(Hours6,0) + isnull(Hours7,0) + isnull(Hours8,0) + isnull(Hours9,0) + isnull(Hours10,0) + isnull(Hours11,0) + isnull(Hours12,0)

and the same logic for Dollars?

 

[yelworcm]

I found a novel way of unpivoting a while back (maybe on StackOverflow). Try this:

select e.Employee a.Month, a.Hours, a.Dollars
from employees e cross apply
    (values (1, e.hours1, e.dollars1),
          (2, e.hours2, e.dollars2),
          (3, e.hours3, e.dollars3),
          (4, e.hours4, e.dollars4),
          (5, e.hours5, e.dollars5),
          (6, e.hours6, e.dollars6),
          (7, e.hours7, e.dollars7),
          (8, e.hours8, e.dollars8),
          (9, e.hours9, e.dollars9),
          (10, e.hours10, e.dollars10),
          (11, e.hours11, e.dollars11),
          (12, e.hours12, e.dollars12)) a (month, hours, dollars)

This will require at least sql 2008, maybe R2. I am not sure.

 

[gmmastros]

I always preferred the "old school" method for unpivoting. This method will work on any version of SQL (and probably any database engine).

Select	DetailKey,
		'Jul' As [Month],
		Hours01 As Hours,
		Dollars01 As Dollars
From    LG_SYSTEM.dbo.T_EMPLOYEE_RESULTS_MONTH_DETAIL

Union All

Select DetailKey,
       'Aug' As [Month],
       Hours02 As Hours,
       Dollars02 As Dollars
From   LG_SYSTEM.dbo.T_EMPLOYEE_RESULTS_MONTH_DETAIL

Union All

Select DetailKey,
       'Sep' As [Month],
       Hours03 As Hours,
       Dollars03 As Dollars
From   LG_SYSTEM.dbo.T_EMPLOYEE_RESULTS_MONTH_DETAIL

Union All

Select DetailKey,
       '[!]Oct[/!]' As [Month],
       Hours[!]04[/!] As Hours,
       Dollars[!]04[/!] As Dollars
From   LG_SYSTEM.dbo.T_EMPLOYEE_RESULTS_MONTH_DETAIL

etc....

All you need to do is complete the code above for all 12 months while changing the part the I highlighted in red.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom