SQL Pivot Query
I need to keep all the fields below
Except
I need to create a column for each DefCode type and the hours under that def code
The grouping will be on
hoursTrackingID
Assignment Number
Week Ending Date
Employee Number
Employee First Name
Employee Last Name
Except
I need to create a column for each DefCode type and the hours under that def code
The grouping will be on
hoursTrackingID
Assignment Number
Week Ending Date
Employee Number
Employee First Name
Employee Last Name
ASKER
PortletPaul
It changes per person
It changes per person
A simple GROUP BY with SUM() can transpose the data: for example:
SELECT
hoursTrackingID
, [Assignment Number]
, [Week Ending Date]
, [Employee Number]
, [Employee First Name]
, [Employee Last Name]
, SUM(case when DefCode = 'RG' then PayHours else 0 end) as RG
, SUM(case when DefCode = 'OT' then PayHours else 0 end) as OT
, SUM(case when DefCode = 'On Call' then PayHours else 0 end) as [On Call]
, SUM(case when DefCode = 'Call Back' then PayHours else 0 end) as [Call Back]
FROM SomeTable
--WHERE <<whatever is needed here>>
GROUP BY hoursTrackingID
, [Assignment Number]
, [Week Ending Date]
, [Employee Number]
, [Employee First Name]
, [Employee Last Name]
>>It changes per person
:) not quite what I wanted, LOL. But a perfectly reasonable reply, my fault.
In total, do you just need 4 columns?
--------------------------
| RG | OT | On Call | Call Back |
--------------------------
:) not quite what I wanted, LOL. But a perfectly reasonable reply, my fault.
In total, do you just need 4 columns?
--------------------------
| RG | OT | On Call | Call Back |
--------------------------
ASKER
Correct
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks
No problem, thanks for the grading. Cheers, Paul
RG
OT
On Call
Call Back
or does the number of DefCode values change each time?