How to transpose rows into columns in sql.

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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 3-Jun-18 18:48pm

SukirtiShetty

Updated 6-Jun-18 22:13pm

v4

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2018-06-03T19:38:00

Solution 1

See here: Using PIVOT and UNPIVOT | Microsoft Docs[^] and here: Simple Way To Use Pivot In SQL Query[^]

Posted 3-Jun-18 19:38pm

OriginalGriff

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

Maciej Los · Answer 2 · 2018-06-06T22:15:00

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):

SQL

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[^]