June 2, 2020 at 3:48 pm
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
I have been beating my head against the wall for a few weeks now with no luck. Any help is appreciated
June 2, 2020 at 4:15 pm
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
June 2, 2020 at 5:39 pm
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.
June 2, 2020 at 6:05 pm
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.)
June 2, 2020 at 8:58 pm
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!!
June 2, 2020 at 10:04 pm
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
Change is inevitable... Change for the better is not.
June 3, 2020 at 12:31 am
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;
June 3, 2020 at 12:46 am
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
Change is inevitable... Change for the better is not.
June 3, 2020 at 2:54 am
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;
June 3, 2020 at 2:22 pm
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;
June 3, 2020 at 2:47 pm
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.
June 3, 2020 at 2:47 pm
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
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply