Looking for help on transpose the result to be improved with pivot

Question

Post reply

Looking for help on transpose the result to be improved with pivot

Viewing 8 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Here's an example on what you might need. The explanation can be found in the following article:

http://www.sqlservercentral.com/articles/T-SQL/63681/

It's incomplete and untested, so you need to complete it and test the results are correct.

SELECT [ProjectName],

[Employee Number],

SUM(CASE WHEN CutoffMonth='Nov 2015' THEN TotalHRSWORKED END) 'Nov 2015',

SUM(CASE WHEN CutoffMonth='Dec 2015' THEN TotalHRSWORKED END) 'Dec 2015',

SUM(CASE WHEN CutoffMonth='Jan 2016' THEN TotalHRSWORKED END) 'Jan 2016',

HourlyRate

FROM tfjds

GROUP BY [ProjectName],

[Employee Number],

HourlyRate;

I'd really change the CutoffMonth column to a date data type. Meanwhile, you can shorten your ORDER BY clause to

Order By CAST(CutoffMonth AS datetime) Asc

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

PasLe Choix Say Hey Kid Points: 665 More actions · Answer 2

Thank you very much for your quick reply, for unknown reason, the sum produces only CutoffMonth itself like 'Jan 2016', 'Feb 2016' instead of actually summed numbers.

PasLe Choix Say Hey Kid Points: 665 More actions · Answer 3

My code again here:

Select CutoffMonth,

ROW_NUMBER() OVER(ORDER BY [ProjectName] ASC) AS ProjectID, [Employee Number],

'Nov 2015',

'Dec 2015',

'Jan 2016',

TotalHRSWORKED, HourlyRate

From (Select distinct

CutoffMonth,

[ProjectName],

[Employee Number],

SUM(CASE WHEN [Mois Imputé:Cutoff Month]='Nov 2015' THEN [Nombre d'Heures Approuvées par Mois:Number of Hours Approved per] ELSE 0 END) As 'Nov 2015',

SUM(CASE WHEN [Mois Imputé:Cutoff Month]='Dec 2015' THEN [Nombre d'Heures Approuvées par Mois:Number of Hours Approved per] ELSE 0 END) As 'Dec 2015',

SUM(CASE WHEN [Mois Imputé:Cutoff Month]='Jan 2016' THEN [Nombre d'Heures Approuvées par Mois:Number of Hours Approved per] ELSE 0 END) As 'Jan 2016',

TotalHRSWORKED,

HourlyRate

From tfjds) c

Group By CutoffMonth, [ProjectName], [Employee Number], TotalHRSWORKED, HourlyRate

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

PasLe Choix (10/20/2016)
Thank you very much for your quick reply, for unknown reason, the sum produces only CutoffMonth itself like 'Jan 2016', 'Feb 2016' instead of actually summed numbers.

I have no idea of why is that happening. I just made a guess based on your query. To get an explanation, post the query you used and DDL with sample data. Learn how to do it by reading the articles in my signature.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

PasLe Choix (10/20/2016)
My code again here:
Select CutoffMonth,
ROW_NUMBER() OVER(ORDER BY [ProjectName] ASC) AS ProjectID, [Employee Number],
'Nov 2015',
'Dec 2015',
'Jan 2016',
TotalHRSWORKED, HourlyRate
From (Select distinct
CutoffMonth,
[ProjectName],
[Employee Number],
SUM(CASE WHEN [Mois Imputé:Cutoff Month]='Nov 2015' THEN [Nombre d'Heures Approuvées par Mois:Number of Hours Approved per] ELSE 0 END) As 'Nov 2015',
SUM(CASE WHEN [Mois Imputé:Cutoff Month]='Dec 2015' THEN [Nombre d'Heures Approuvées par Mois:Number of Hours Approved per] ELSE 0 END) As 'Dec 2015',
SUM(CASE WHEN [Mois Imputé:Cutoff Month]='Jan 2016' THEN [Nombre d'Heures Approuvées par Mois:Number of Hours Approved per] ELSE 0 END) As 'Jan 2016',
TotalHRSWORKED,
HourlyRate
From tfjds) c
Group By CutoffMonth, [ProjectName], [Employee Number], TotalHRSWORKED, HourlyRate

That's because you're using string literals. You can use single quotes when assigning but not when referencing them as columns. For that, you need the square braquets. You also changed the query structure and you're using an incorrect GROUP BY clause.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

PasLe Choix Say Hey Kid Points: 665 More actions · Answer 6

I fixed it, thank you very much.

Can you explain how to do this using the PIVOT function?

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 7

PasLe Choix (10/20/2016)
I fixed it, thank you very much.
Can you explain how to do this using the PIVOT function?

It will be slower, in most cases.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

halifaxdal SSCoach Points: 19741 More actions · Answer 8

I kind of remember I saw Jeff published a post somewhere about pivot, but just couldn't find it any more, maybe Jeff can post the link here again? Thanks.