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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Kevin CrossChief Technology OfficerCommented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.