Click here to Skip to main content
15,908,776 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to display datewise report of selected month and year. and in my below query it worked. But the problem is i want to transpose the date Rows into Columns.

Please anyone help me to resolve this.

What I have tried:

    (COALESCE(SUM(CONVERT(INT, d.duration*6.25)), 0)) + (COALESCE(SUM(CONVERT(INT, b.image_count)), 0)) +(COALESCE(SUM(CONVERT(INT, e.image_count)), 0)/4) + (COALESCE(SUM(CONVERT(INT, f.doc_count*(cast(f.fieldscount as int)))), 0)) 
     AS Total
    work a
    LEFT JOIN work d ON a.resources = d.resources and a.Workid_PK=d.workid_PK AND (d.scope = 'Technical Services' and d.work_area='other') AND a.dat_e=d.dat_e AND YEAR(d.dat_e) = '2018' AND (MONTH(d.dat_e) ='05') 
    LEFT JOIN work b ON a.resources = b.resources and a.Workid_PK=b.workid_PK AND (b.scope = 'LDD' AND b.Work_area='Coding') and a.dat_e=b.dat_e AND YEAR(b.dat_e) = '2018' AND (MONTH(b.dat_e)='05') 
    LEFT JOIN work e ON a.resources = e.resources and a.Workid_PK=e.workid_PK AND (e.scope = 'LDD' AND e.Work_area='QC') and a.dat_e=e.dat_e and YEAR(e.dat_e) = '2018' AND (MONTH(e.dat_e) ='05') 
    LEFT JOIN work f ON a.resources = f.resources and a.Workid_PK=f.workid_PK AND (f.scope = 'Coding' AND f.Work_area='Coding') and a.dat_e=f.dat_e AND YEAR(f.dat_e) = '2018' AND (MONTH(f.dat_e) ='05') 
where (YEAR(a.dat_e) = '2018' AND MONTH(a.dat_e) ='05')
    a.Resources, a.dat_e

Output is:

John	5/1/2018		0
Ram	5/14/2018	     2000
John	5/2/2018		0
Ram	5/3/2018		0
Philip	5/10/2018	     8484
Prince	5/6/2018		0
John	5/4/2018		0
Ram	5/5/2018		0
John	5/6/2018		0
Ram	5/7/2018		0
Philip	5/8/2018		0
Prince	5/9/2018		0

but i want to transpose the date column as header
anyone Please help me to resolve this.

Res	5/1/2018	5/2/2018	5/3/2018	5/4/2018	5/5/2018	5/6/2018	5/7/2018	5/8/2018	5/9/2018	5/10/2018	5/11/2018	5/12/2018	5/13/2018	5/14/2018
Ram														2000
John		500												
Philip										8484				
Updated 6-Jun-18 22:13pm

Share this answer
SukirtiShetty 5-Jun-18 1:20am    
I am able to get the particular months report.
but anyone please help me to transpose the Date column
Well, based on the documentation provided by OriginalGriff, you can achieve that by using 'static' version of pivot (you have to type the names of columns for each date):
SELECT Resources, [5/1/2018],	[5/2/2018],	[5/3/2018], ... [5/13/2018], [5/14/2018]
    --Your query goes here!
) AS Src
PIVOT(Total FOR dat_e IN([5/1/2018],	[5/2/2018],	[5/3/2018], ... [5/13/2018], [5/14/2018])) AS PVT

For 'dynamic' version, where the column-names for date are created in a run-time, please see:
Script to create dynamic PIVOT queries in SQL Server[^]
SQL Script: Convert Rows To Columns Using Dynamic Pivot In SQL Server - TechNet Articles - United States (English) - TechNet Wiki[^]
Dynamic PIVOT in Sql Server |[^]
Dynamic PIVOT query in SQL Server - SQLRelease[^]
Dynamic Pivot Query in SQL Server[^]
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900