Post reply

SQL Pivot Query

  • 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.

  • January 30, 2017 at 10:44 am

    #1925668

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

  • January 30, 2017 at 11:29 am

    #1925696

    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

  • January 30, 2017 at 12:33 pm

    #1925712

    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

  • January 30, 2017 at 1:17 pm

    #1925720

    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)
    Intro to Tally Tables and Functions

  • January 30, 2017 at 2:02 pm

    #1925729

    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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • January 30, 2017 at 4:58 pm

    #1925754

    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)
    Intro to Tally Tables and Functions

  • January 31, 2017 at 1:54 am

    #1925783

    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,

  • February 3, 2017 at 3:05 am

    #1926540

    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.

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

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