Click here to Skip to main content
14,699,541 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi
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:

SELECT 
    a.Resources,a.dat_e,
     
    (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
FROM 
    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')
   
GROUP BY
    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				
Posted
Updated 6-Jun-18 23:13pm
v4

   
Comments
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]
FROM (
    --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 | SqlHints.com[^]
Dynamic PIVOT query in SQL Server - SQLRelease[^]
Dynamic Pivot Query in SQL Server[^]
   

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