Pivot Table Problem

Question

Pivot Table Problem

SSCrazy

Points: 2678

May 20, 2020 at 2:25 pm

I am having a huge brain fart on how to accomplish what I want. I want to pivot the attached set of data (sql file) to look like this

Annotation 2020-05-20 102257

(I know its an image but I am not savy enough to plop an actual result set in here).

The attached file creates a temp file with all of the data in it.

I got this far before coming to the boards

SELECT * FROM (
select T1.AllergyName ,ValueName = col, value  from dbo.AirAndPollenScale T1
cross apply (select 'ScaleStart', convert(nvarchar(100), ISNULL(ScaleStart, -1)) UNION ALL
SELECT 'ScaleEnd', convert(nvarchar(100), ISNULL(ScaleEnd, -1)) UNION ALL
SELECT 'ScaleValue', convert(nvarchar(100), ScaleValue) ) c  (col, value)
) d
PIVOT (min(value)
for ValueName in ([ScaleStart],
[ScaleEnd],
[ScaleValue])) piv;

IF NOT ('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 (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 (17, N'AirQuality', 0, 50, N'Good')
INSERT #AirAndPollenScale ([AllergyScaleID], [AllergyName], [ScaleStart], [ScaleEnd], [ScaleValue]) VALUES (18, N'AirQuality', 51, 100, N'Moderate')
INSERT #AirAndPollenScale ([AllergyScaleID], [AllergyName], [ScaleStart], [ScaleEnd], [ScaleValue]) VALUES (23, N'UVIndex', 1, 2, N'Low')
INSERT #AirAndPollenScale ([AllergyScaleID], [AllergyName], [ScaleStart], [ScaleEnd], [ScaleValue]) VALUES (24, N'UVIndex', 3, 5, N'Moderate')

This topic was modified 4 years, 6 months ago by SQL Espo.

Viewing 3 posts - 1 through 2 (of 2 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736173 More actions · Answer 1

Please post the code with a repro that has some data. We don't allow .sql files to be uploaded.

SQL Espo SSCrazy Points: 2678 More actions · Answer 2

Please let me know if how I added the inserts to my original post is okay, and if its not, please tell me what you mean by "repo". I assume you mean a GitHub repo, but want to make sure 🙂