CODEWITHSUNDEEP
sqlsql-serverssrs-2008
SJD
SJD

Reputation: 63

how to convert rows to columns using sql query

Pls see the below query 

 select D.emp_id, E.emp_name, A.proj_code, B.proj_name, A.activity_code, C.activity_name,
CONVERT(CHAR(4), A.effort_date, 100) + CONVERT(CHAR(4), A.effort_date, 120) as Month, A.effort_hours
from timesheet_activity A left join
project_master B on A.proj_code = B.proj_code
left join activity_master C on A.activity_code = C.activity_code
left join timesheet D on A.timesheet_id = D.timesheet_id
left join employee_master E on D.emp_id = E.emp_id
WHERE B.proj_name IN ('Sales Support') and (A.effort_date BETWEEN (SELECT CONVERT(VARCHAR(10), CONVERT(date, '30-06-2014', 105), 23))  AND (SELECT CONVERT(VARCHAR(10), CONVERT(date, '02-07-2014', 105), 23)))

The result will be like this.

enter image description here

Now what I want to show is as below.

emp_id  emp_name  proj_code  activity_code  activity_name Jun 2014  Jul 2014
101257  Chris      1001       9001           Test          1.5         1

I want to make the months as columns and show efforts hours for the same.

Any suggestions would be highly appreciated

Upvotes: 2

Views: 66

Answers (1)

LONG
LONG

Reputation: 4620

WITH ABC AS ( paste your query to get that result here ) Select * FROM ABC PIVOT (MAX(effort_hours) for Month in ([Jun 2014],[Jul 2014])) as MaxHrs

You could change the function MAX to any math function you want for selecting condition

Upvotes: 1

Related Questions