Custom Pivot Table

  • I have an old post about this but I did not do it correctly. So here is my attempt to follow the rules 🙂

    I have a table that I want to pivot.

    The Script to create a temporary table and insert the data is here

    https://www.dropbox.com/s/h6l1c3h6vf0eewb/AirAndPollenScale.sql?dl=0

    I want to pivot so the result set looks like this

    Annotation 2020-06-02 114544

    I have been beating my head against the wall for a few weeks now with no luck. Any help is appreciated

  • IF NOT OBJECT_ID('tempdb..#AirAndPollenScale') IS NULL
    DROP TABLE #AirAndPollenScale

    CREATE TABLE #AirAndPollenScale(
    [AllergyScaleID] [int],
    [AllergyName] [nvarchar](max) NULL,
    [ScaleStart] [int] NULL,
    [ScaleEnd] [int] NULL,
    [ScaleValue] [nvarchar](100) NULL,
    )


    INSERT #AirAndPollenScale ([AllergyScaleID], [AllergyName], [ScaleStart], [ScaleEnd], [ScaleValue]) VALUES (1, N'Mold', 1, 6499, N'Low')

    INSERT #AirAndPollenScale ([AllergyScaleID], [AllergyName], [ScaleStart], [ScaleEnd], [ScaleValue]) VALUES (2, N'Mold', 6500, 12999, N'Moderate')

    INSERT #AirAndPollenScale ([AllergyScaleID], [AllergyName], [ScaleStart], [ScaleEnd], [ScaleValue]) VALUES (3, N'Mold', 13000, 49999, N'High')

    INSERT #AirAndPollenScale ([AllergyScaleID], [AllergyName], [ScaleStart], [ScaleEnd], [ScaleValue]) VALUES (4, N'Mold', 50000, NULL, N'Very High')

    INSERT #AirAndPollenScale ([AllergyScaleID], [AllergyName], [ScaleStart], [ScaleEnd], [ScaleValue]) VALUES (5, N'Grass', 1, 4, N'Low')

    INSERT #AirAndPollenScale ([AllergyScaleID], [AllergyName], [ScaleStart], [ScaleEnd], [ScaleValue]) VALUES (6, N'Grass', 5, 19, N'Moderate')

    INSERT #AirAndPollenScale ([AllergyScaleID], [AllergyName], [ScaleStart], [ScaleEnd], [ScaleValue]) VALUES (7, N'Grass', 20, 199, N'High')

    INSERT #AirAndPollenScale ([AllergyScaleID], [AllergyName], [ScaleStart], [ScaleEnd], [ScaleValue]) VALUES (8, N'Grass', 200, NULL, N'Very High')

    INSERT #AirAndPollenScale ([AllergyScaleID], [AllergyName], [ScaleStart], [ScaleEnd], [ScaleValue]) VALUES (9, N'Tree', 1, 14, N'Low')

    INSERT #AirAndPollenScale ([AllergyScaleID], [AllergyName], [ScaleStart], [ScaleEnd], [ScaleValue]) VALUES (10, N'Tree', 15, 89, N'Moderate')

    INSERT #AirAndPollenScale ([AllergyScaleID], [AllergyName], [ScaleStart], [ScaleEnd], [ScaleValue]) VALUES (11, N'Tree', 90, 1499, N'High')

    INSERT #AirAndPollenScale ([AllergyScaleID], [AllergyName], [ScaleStart], [ScaleEnd], [ScaleValue]) VALUES (12, N'Tree', 1500, NULL, N'Very High')

    INSERT #AirAndPollenScale ([AllergyScaleID], [AllergyName], [ScaleStart], [ScaleEnd], [ScaleValue]) VALUES (13, N'Weed', 1, 9, N'Low')

    INSERT #AirAndPollenScale ([AllergyScaleID], [AllergyName], [ScaleStart], [ScaleEnd], [ScaleValue]) VALUES (14, N'Weed', 10, 49, N'Moderate')

    INSERT #AirAndPollenScale ([AllergyScaleID], [AllergyName], [ScaleStart], [ScaleEnd], [ScaleValue]) VALUES (15, N'Weed', 50, 499, N'High')

    INSERT #AirAndPollenScale ([AllergyScaleID], [AllergyName], [ScaleStart], [ScaleEnd], [ScaleValue]) VALUES (16, N'Weed', 500, NULL, N'Very High')


    SELECT * FROM #AirAndPollenScale
  • Thanks Pietliden for putting the code in a consumable format without having to jump out to a 3rd party website.

    SQL Espo, what have you tried already?  Posting what you have tried is always a good way to start on these forums.  Gives us an idea of how close you were to the solution and helps us put it in the same format you expect.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • I'll give you a hint... MAX( CASE WHEN...)

    It's a LOT of that.

    Jeff Moden posted an article on Crosstabs... that might help too. (Don't want to give the game away... I want to you learn how to do it.)

  • Thank you both so much! I was trying to be cute and figure out how to use the PIVOT operation, but going back to the basics and using the https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-%E2%80%93-converting-rows-to-columns-1

    wuth the CASE statements made it so much easier!!

  • SQL Espo wrote:

    Thank you both so much! I was trying to be cute and figure out how to use the PIVOT operation, but going back to the basics and using the https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-%E2%80%93-converting-rows-to-columns-1

    wuth the CASE statements made it so much easier!!

    Excellent.  What did you end up with for code to solve your original problem?

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

  • This is how I did it...

    SELECT AllergyName
    ,LowStart = MAX(CASE WHEN ScaleValue = 'Low' THEN ScaleStart END)
    ,LowEnd = MAX(CASE WHEN ScaleValue = 'Low' THEN ScaleEnd END)
    ,ModerateStart = MAX(CASE WHEN ScaleValue ='Moderate' THEN ScaleStart END)
    ,ModerateEnd = MAX(CASE WHEN ScaleValue = 'Moderate' THEN ScaleEnd END)
    ,HighStart = MAX(CASE WHEN ScaleValue ='High' THEN ScaleStart END)
    ,HighEnd = MAX(CASE WHEN ScaleValue = 'High' THEN ScaleEnd END)
    ,VeryHighStart = MAX(CASE WHEN ScaleValue ='Very High' THEN ScaleStart END)
    ,VeryHighEnd = MAX(CASE WHEN ScaleValue = 'Very High' THEN ScaleEnd END)
    FROM #AirAndPollenScale;
  • Mine is similar but I took advantage of IIF.  It still renders out as CASE expressions behind the scenes so no performance gain or anything like that.  I also think you need to slip a GROUP BY into your code... you probably missed copying that line.

     SELECT  AllergyName
    ,LowStart = MAX(IIF(ScaleValue = 'Low' ,ScaleStart,NULL))
    ,LowEnd = MAX(IIF(ScaleValue = 'Low' ,ScaleEnd ,NULL))
    ,ModerateStart = MAX(IIF(ScaleValue = 'Moderate' ,ScaleStart,NULL))
    ,ModerateEnd = MAX(IIF(ScaleValue = 'Moderate' ,ScaleEnd ,NULL))
    ,HighStart = MAX(IIF(ScaleValue = 'High' ,ScaleStart,NULL))
    ,HighEnd = MAX(IIF(ScaleValue = 'High' ,ScaleEnd ,NULL))
    ,VeryHighStart = MAX(IIF(ScaleValue = 'Very High',ScaleStart,NULL))
    ,VeryHighEnd = MAX(IIF(ScaleValue = 'Very High',ScaleEnd ,NULL))
    FROM #AirAndPollenScale
    GROUP BY AllergyName
    ;

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

  • Oops, yes, I did miss it.  This is what I used. (Funny I would get the answer right and then miss copying it... the easy part!)

    SELECT AllergyName
    ,LowStart = MAX(CASE WHEN ScaleValue = 'Low' THEN ScaleStart END)
    ,LowEnd = MAX(CASE WHEN ScaleValue = 'Low' THEN ScaleEnd END)
    ,ModerateStart = MAX(CASE WHEN ScaleValue ='Moderate' THEN ScaleStart END)
    ,ModerateEnd = MAX(CASE WHEN ScaleValue = 'Moderate' THEN ScaleEnd END)
    ,HighStart = MAX(CASE WHEN ScaleValue ='High' THEN ScaleStart END)
    ,HighEnd = MAX(CASE WHEN ScaleValue = 'High' THEN ScaleEnd END)
    ,VeryHighStart = MAX(CASE WHEN ScaleValue ='Very High' THEN ScaleStart END)
    ,VeryHighEnd = MAX(CASE WHEN ScaleValue = 'Very High' THEN ScaleEnd END)
    FROM #AirAndPollenScale
    GROUP BY AllergyName;
  • My solution was

    select AllergyName,
    MAX(CASE WHEN ScaleValue = 'Low' THEN ScaleStart else 0 end) AS [LowStart],
    MAX(CASE WHEN ScaleValue = 'Low' THEN ScaleEnd else 0 end) AS [LowEnd],
    MAX(CASE WHEN ScaleValue = 'Moderate' THEN ScaleStart else 0 end) AS [ModerateStart],
    MAX(CASE WHEN ScaleValue = 'Moderate' THEN ScaleEnd else 0 end) AS [ModerateEnd],
    MAX(CASE WHEN ScaleValue = 'High' THEN ScaleStart else 0 end) AS [HighStart],
    MAX(CASE WHEN ScaleValue = 'High' THEN ScaleEnd else 0 end) AS [HighEnd],
    MAX(CASE WHEN ScaleValue = 'Very High' THEN ScaleStart else 0 end) AS [VeryHighStart],
    MAX(CASE WHEN ScaleValue = 'Very High' THEN ScaleEnd else 0 end) AS [VeryHighEnd]
    from #AirAndPollenScale
    group by AllergyName;
  • Same as mine, except I put the column names at the beginning.

    Glad you sorted it out. I didn't want to just give you the answer, because then you wouldn't learn anything.

  • SQL Espo wrote:

    My solution was

    select AllergyName,
    MAX(CASE WHEN ScaleValue = 'Low' THEN ScaleStart else 0 end) AS [LowStart],
    MAX(CASE WHEN ScaleValue = 'Low' THEN ScaleEnd else 0 end) AS [LowEnd],
    MAX(CASE WHEN ScaleValue = 'Moderate' THEN ScaleStart else 0 end) AS [ModerateStart],
    MAX(CASE WHEN ScaleValue = 'Moderate' THEN ScaleEnd else 0 end) AS [ModerateEnd],
    MAX(CASE WHEN ScaleValue = 'High' THEN ScaleStart else 0 end) AS [HighStart],
    MAX(CASE WHEN ScaleValue = 'High' THEN ScaleEnd else 0 end) AS [HighEnd],
    MAX(CASE WHEN ScaleValue = 'Very High' THEN ScaleStart else 0 end) AS [VeryHighStart],
    MAX(CASE WHEN ScaleValue = 'Very High' THEN ScaleEnd else 0 end) AS [VeryHighEnd]
    from #AirAndPollenScale
    group by AllergyName;

    That works!  Thanks for the feedback.  I just wanted to make sure (think of it as a remote "code review").  Do you have any questions about how this ancient CROSSTAB method works?

     

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

Viewing 12 posts - 1 through 11 (of 11 total)

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