SQL Pivot Query

Question

Post reply

SQL Pivot Query

paul 69259

Hall of Fame

Points: 3068
More actions
January 30, 2017 at 10:27 am

#401517

Hi, I have managed to get a pivot using the following code
Select SALESDESCRIPTION, GROUPDESCRIPTION, ACCOUNTNUMBER, ACCOUNTNAME, SUM(NP8) AS NP8, SUM(SO) AS SO, SUM(SP) AS SP, SUM(Total) AS Total FROM ( SELECT dbo.Sales.SALESDESCRIPTION, dbo.Groups.GROUPDESCRIPTION, dbo.Account.ACCOUNTNUMBER, dbo.Account.ACCOUNTNAME, dbo.Orders.ORDERDATEANDTIME, Case When Type = 0 THEN 1 ELSE 0 END AS SP, Case When Type = 3 THEN 1 ELSE 0 END AS NP8, Case When Type = 4 THEN 1 ELSE 0 END AS SO, CASE WHEN Type IN (0,3,4) THEN 1 ELSE 0 END AS Total FROM dbo.Products INNER JOIN dbo.Orders ON dbo.Products.PRODUCTID = dbo.Orders.PRODUCTID INNER JOIN dbo.Account ON dbo.Orders.ACCOUNTID = dbo.Account.ACCOUNTID INNER JOIN dbo.Groups ON dbo.Account.GROUPID = dbo.Groups.GROUPID CROSS JOIN dbo.Sales Where Type IN (0,3,4) AND ORDERDATEANDTIME >= Convert(dateTime,'01/01/2016',103) AND ORDERDATEANDTIME < Convert(dateTime,'01/01/2017',103) ) AS Source Group By SALESDESCRIPTION, GROUPDESCRIPTION, ACCOUNTNUMBER, ACCOUNTNAME Order By SALESDESCRIPTION, GROUPDESCRIPTION, ACCOUNTNUMBER
I have a slight problem in that I need to be able to do a second pivot I think. What I need to show is the Month and then have the total for each type (SP,NP8 and SO) for the month. I have attached a pic of how the report needs to look. Any idea how I can go about this?
Thank you
Paul.

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

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

Can you post the DDL (create table) script, sample data as an insert statement and the expected results please?
😎

drew.allen SSC Guru Points: 76985 More actions · Answer 2

You can do this in T-SQL, but it gets complicated very quickly. I would recommend that you use a reporting tool such as SSRS to produce these kinds of reports.

That being said, what you need to do is have a CASE expression for every single new column that you want (including totals/subtotals) and each CASE expression will need to be tailored to the specific subset that you want for that column. For example, you'll need an CASE expression where the month is January and the type is NP8, ad nauseum.

Also, this technique is generally referred to as a CROSSTAB. PIVOT is usually reserved for queries that use the PIVOT keyword.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

Do a web search for dynamic pivot sql server for some options.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

drew.allen - Monday, January 30, 2017 11:29 AM
I would recommend that you use a reporting tool such as SSRS to produce these kinds of reports.

I have to admit a bit of ignorance in the area of SSRS. Can you give me a hint as to how this might be done in SSRS without having to change something in the report every month? For example, is it as easy as an automatic pivot/transpose in Excel?

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

Jeff Moden - Monday, January 30, 2017 1:17 PM
drew.allen - Monday, January 30, 2017 11:29 AM
I would recommend that you use a reporting tool such as SSRS to produce these kinds of reports.
I have to admit a bit of ignorance in the area of SSRS. Can you give me a hint as to how this might be done in SSRS without having to change something in the report every month? For example, is it as easy as an automatic pivot/transpose in Excel?

SSRS takes care of the dynamic pivot using a control called a 'tablix'. You can read more here ... and you'll find many many other references if you search for yourself.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

Very cool, Phil. Now I know what to look for. I appreciate it.

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

paul 69259 Hall of Fame Points: 3068 More actions · Answer 7

Thank you all for the replies. The view I was creating was to be accessed via SSRS. Now I know SSRS can take care of the totals for the month on it's own I will see if I can do it in there,

paul 69259 Hall of Fame Points: 3068 More actions · Answer 8

Jeff Moden - Monday, January 30, 2017 1:17 PM
drew.allen - Monday, January 30, 2017 11:29 AM
I would recommend that you use a reporting tool such as SSRS to produce these kinds of reports.
I have to admit a bit of ignorance in the area of SSRS. Can you give me a hint as to how this might be done in SSRS without having to change something in the report every month? For example, is it as easy as an automatic pivot/transpose in Excel?

Hi Jeff,

I have completed the report in ssrs. You group the Rows together (in my case salesdecription, groupdescription,accountnumber and accountname) then you have the column groups (in my case month, NP8, SO, SP and Total) The report then more or less does it all for you. Add some parameters for the customer to filter the report by and to cope with the changing years and new salesdescriptions being added I added a seperate dataset for each and set the available values to be from a query on the view (eg. select distinct(salesdescription) from quartzreport1). For me it's a lot easier to work in SSRS than SQL but I am only just learning.

Thank you
Paul.