CODEWITHSUNDEEP
sqltoad
Sharif Mia
Sharif Mia

Reputation: 111

Transpose row values into columns

I have a sample query that returns:

First screenshot

The query is:

SELECT departmentID, departmentMembers, workingHours
FROM timeTracker
WHERE departmentID = 11111

Basically, I want to transpose the values of departmentMembers column and make them into new columns and the values for each column of workingHours will be the values those columns. See the second screenshot for what I want. Notice that departmentMembers column is not there anymore in the following screenshot:

Second Screenshot

Upvotes: 0

Views: 249

Answers (2)

Naveed Ahmed
Naveed Ahmed

Reputation: 493

use pivot table to transpose column

select departmentID, [ADMIN_FEE],[AGT_COMM],[AGT_OVER],[IRPM],[NCB_A],[VIP_FEE] from (SELECT departmentID, departmentMembers, workingHours FROM timeTracker WHERE departmentID = 11111) as SourceTable Pivot (max(workingHours) for departmentMembers IN ([ADMIN_FEE],[AGT_COMM],[AGT_OVER],[IRPM],[NCB_A],[VIP_FEE]) AS PivotTable;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can use conditional aggregation:

select departmentID, 
       sum(case when departmentMembers = 'ADMIN_FEE' then workingHours else 0 end) as admin_fee,
       . . .
from timeTracker
where departmentID = 11111
group by departmentID;

The . . . is for the remaining columns.

Upvotes: 1

Related Questions