October 20, 2016 at 1:03 pm
Hello,
I understand using pivot must have been discussed many times here, please pardon my request as I barely do pivot in the past and this is an urgent request from my new boss.
I have a result using query like below:
Select CutoffMonth,
ROW_NUMBER() OVER(ORDER BY [ProjectName] ASC) AS ProjectID, [Employee Number], TotalHRSWORKED, HourlyRate
From (Select distinct
CutoffMonth,
[ProjectName],
[Employee Number],
TotalHRSWORKED,
HourlyRate
From tfjds) c
Group By CutoffMonth, [ProjectName], [Employee Number], TotalHRSWORKED, HourlyRate
Order By case
when CutoffMonth='Nov 2015' then 1
when CutoffMonth='Dec 2015' then 2
when CutoffMonth='Jan 2016' then 3
when CutoffMonth='Feb 2016' then 4
when CutoffMonth='Mar 2016' then 5
when CutoffMonth='Apr 2016' then 6
when CutoffMonth='May 2016' then 7
when CutoffMonth='Jun 2016' then 8
when CutoffMonth='Jul 2016' then 9
when CutoffMonth='Aug 2016' then 10
when CutoffMonth='Sep 2016' then 11
when CutoffMonth='Oct 2016' then 12
end Asc
The result needs to be actually in a transposed way to show the following columns:
[ProjectName], [Employee Number], 'Nov 2015', 'Dec 2015', 'Jan 2016', 'Feb 2016', 'Mar 2016', 'Apr 2016', 'May 2016' ,'Jun 2016' ,'Jul 2016', 'Aug 2016', 'Sep 2016', 'Oct 2016' , TotalHRSWORKED, HourlyRate
Can anyone help please? Thank you very much in advance.
October 20, 2016 at 1:13 pm
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
October 20, 2016 at 1:29 pm
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.
October 20, 2016 at 1:32 pm
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
October 20, 2016 at 1:33 pm
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.
October 20, 2016 at 1:37 pm
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.
October 20, 2016 at 1:48 pm
I fixed it, thank you very much.
Can you explain how to do this using the PIVOT function?
October 20, 2016 at 3:58 pm
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
Change is inevitable... Change for the better is not.
October 20, 2016 at 7:47 pm
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply