SQL PIVOT - not sure how?

Can anyone assist with pivoting this please?
SELECT
	DATENAME(MM, Jobs.DateTimeReceived) + ' ' + CAST(YEAR(Jobs.DateTimeReceived) AS VARCHAR(4)) AS ReportPeriod,
	COUNT(Jobs.JobId) As [TotalNumberOfInstructionsReceived],
	SUM(CASE WHEN Jobs.StatusId = 10 THEN 1 ELSE 0 END) As [NumberOfInstructionsConverted],
	COUNT(Jobs.JobId) - SUM(CASE WHEN Jobs.StatusId = 10 THEN 1 ELSE 0 END) As [NumberOfInstructionsOutstanding],
	SUM(CASE WHEN Jobs.StatusId = 10 THEN 1 ELSE 0 END) As [TotalNumberOfInvoices],
	SUM(CASE WHEN vwRF_CompletionSums.SumOfMaterials = 0 THEN 1 ELSE 0 END) As [TotalNumberOfRepairInvoices],
	SUM(CASE WHEN vwRF_CompletionSums.SumOfMaterials > 0 THEN 1 ELSE 0 END) As [TotalNumberOfReplacementInvoices],
	SUM(vwRF_CompletionSums.SumOfGross) / 1.15 As [TotalCostOfInvoices],
	SUM(CASE WHEN vwRF_CompletionSums.SumOfMaterials = 0 THEN vwRF_CompletionSums.SumOfGross / 1.15 ELSE 0 END) As [TotalCostOfRepairInvoices],
	SUM(CASE WHEN vwRF_CompletionSums.SumOfMaterials > 0 THEN vwRF_CompletionSums.SumOfGross / 1.15 ELSE 0 END) As [TotalCostOfReplacementInvoices],
	0 As TotalCLDiscountSavings,
	0 As TotalRepairSavings,
	0 As TotalRepudiationClaimsWithdrawnSavings,
	0 As TotalSavingsAmount,
	0 As AverageSavings
FROM
	Jobs
	INNER JOIN JobStatuses ON JobStatuses.StatusId = Jobs.StatusId
	LEFT OUTER JOIN vwRF_CompletionSums ON vwRF_CompletionSums.JobId = Jobs.JobId
	
WHERE
	Jobs.AccountCode = 75946
	And Jobs.StatusId <> 0
 
GROUP BY
	DATENAME(MM, Jobs.DateTimeReceived) + ' ' + CAST(YEAR(Jobs.DateTimeReceived) AS VARCHAR(4))

Open in new window

LapchienDirectorAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
0
 
Kevin CrossChief Technology OfficerCommented:
What is the current output?  How would you like it to be pivoted?

Usually PIVOT works this way:

SELECT [name], [2009-01], [2009-02], [2009-03],[2009-04], [2009-05]
FROM your_table_name
PIVOT (SUM(quantity) FOR ReportingPeriod IN ([2009-01], [2009-02], [2009-03],[2009-04], [2009-05])) pvt

Note the hardcoded values for column reporting period.
0
 
LapchienDirectorAuthor Commented:
Current output is a single line per period (May 2009, Field1, Field 2 etc).

Needs to have the Period accross the top May 2009, June 2009, July 2009 etc) with the fields down the left...

0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Kevin CrossChief Technology OfficerCommented:
But what is the row heading, please show with actual data just to make sure.  That is what I thought looking at it, but want to make sure as probably can't use PIVOT keyword in that case.
0
 
LapchienDirectorAuthor Commented:
The row headings are as per the basic select query - ReportPeriod etc.
However, I need a pivot to show just the ReportPeriod as column headings, with the rest shown as row headings...  hope that makes sense!?
0
 
Kevin CrossChief Technology OfficerCommented:
Still would like some sample data please.


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.