I have a device database, which contains data on devices, including names, locations, and a number of numeric and string attributes. I am trying to execute a query, pulling only those attributes with numeric values into a pivot table.
When I query the data flatly, the data looks fine. But when I run the PIVOT query on the same dataset, all results are NULL.
Setup:
/* database setup and data insert */
USE [master] ;
GO
IF EXISTS ( SELECT * FROM sys.databases WHERE [name] = N'DeviceDatabase' )
BEGIN
ALTER DATABASE [DeviceDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE ;
DROP DATABASE [DeviceDatabase] ;
END
CREATE DATABASE [DeviceDatabase] ;
GO
USE [DeviceDatabase] ;
GO
CREATE TABLE dbo.Devices
(
[id] INT NOT NULL IDENTITY (1,1)
CONSTRAINT [PK_Devices] PRIMARY KEY CLUSTERED
, [device_name] VARCHAR(50) NOT NULL
, [device_display_name] VARCHAR(50) NOT NULL
, [device_location] VARCHAR(50) NULL
) ;
CREATE TABLE dbo.DeviceGroups
(
[id] INT NOT NULL IDENTITY (1,1)
CONSTRAINT [PK_DeviceGroups] PRIMARY KEY CLUSTERED
, [device_group_name] VARCHAR(50) NOT NULL
) ;
CREATE TABLE dbo.DeviceDeviceGroups
(
[id] INT NOT NULL IDENTITY (1,1)
CONSTRAINT [PK_DeviceDeviceGroups] PRIMARY KEY CLUSTERED
, [device_id] INT NOT NULL
CONSTRAINT [FK_DeviceDeviceGroups_Devices]
FOREIGN KEY REFERENCES dbo.Devices ( [id] )
, [group_id] INT NOT NULL
CONSTRAINT [FK_DeviceDeviceGroups_DeviceGroups]
FOREIGN KEY REFERENCES dbo.DeviceGroups ( [id] )
) ;
CREATE TABLE dbo.DeviceAttributes
(
[id] INT NOT NULL IDENTITY (1,1)
CONSTRAINT [PK_DeviceAttributes] PRIMARY KEY CLUSTERED
, [device_id] INT NOT NULL
CONSTRAINT [FK_DeviceAttributes_Devices]
FOREIGN KEY REFERENCES dbo.Devices ( [id] )
, [attribute_name] VARCHAR(50) NOT NULL
, [attribute_value] VARCHAR(50) NULL
) ;
GO
INSERT
INTO dbo.Devices
( [device_name], [device_display_name], [device_location] )
VALUES ( 'dev001', 'Device 1', 'Location A' )
, ( 'dev002', 'Device 2', 'Location A' )
, ( 'dev003', 'Device 3', 'Location B' )
, ( 'dev004', 'Device 4', 'Location B' ) ;
INSERT
INTO dbo.DeviceGroups
( [device_group_name] )
VALUES ( 'Group 1A' )
, ( 'Group 1B' ) ;
INSERT
INTO dbo.DeviceDeviceGroups
( [device_id], [group_id] )
VALUES ( 1, 1 )
, ( 2, 1 )
, ( 3, 1 )
, ( 4, 1 ) ;
INSERT
INTO dbo.DeviceAttributes
( [device_id], [attribute_name], [attribute_value] )
VALUES ( 1, 'attrib #1', '0.10' )
, ( 1, 'attrib #2', '0.02' )
, ( 1, 'attrib #3', '0.07' )
, ( 1, 'attrib #4', '0.02' )
, ( 2, 'attrib #1', '0.16' )
, ( 2, 'attrib #2', '0.05' )
, ( 2, 'attrib #3', '0.12' )
, ( 2, 'attrib #4', '0.04' )
, ( 3, 'attrib #1', '0.15' )
, ( 3, 'attrib #2', '0.05' )
, ( 3, 'attrib #3', '0.07' )
, ( 3, 'attrib #4', '0.06' )
, ( 4, 'attrib #1', '0.10' )
, ( 4, 'attrib #2', '0.03' )
, ( 4, 'attrib #3', '0.07' )
, ( 4, 'attrib #4', '0.03' ) ;
GO
It is annoying the that attribute_value column is a string rather than numeric, but not all attributes are numeric in nature. (And this is the vendor's schema.)
When I execute a flat query (with CTEs) against the table I get a full set of attribute values.
Query:
/* flat query */
USE [DeviceDatabase] ;
GO
DECLARE @PrinterGroup AS VARCHAR(50) ;
SET @PrinterGroup = 'Group 1A' ;
WITH cte_GroupedDevices AS
(
SELECT d.[id] AS [device_id]
, d.[device_name]
, d.[device_display_name]
, d.[device_location]
, dg.[device_group_name]
FROM dbo.Devices AS d
INNER JOIN dbo.DeviceDeviceGroups AS ddg
ON d.[id] = ddg.[device_id]
INNER JOIN dbo.DeviceGroups AS dg
ON ddg.[group_id] = dg.[id]
WHERE dg.[device_group_name] = @PrinterGroup
)
, cte_AttributedDevices AS
(
SELECT gd.[device_name]
, gd.[device_display_name]
, gd.[device_group_name]
, gd.[device_location]
, da.[attribute_name]
, CAST ( da.[attribute_value] AS DECIMAL (5,2) ) AS [attribute_value]
FROM cte_GroupedDevices AS gd
INNER JOIN dbo.DeviceAttributes AS da
ON gd.[device_id] = da.[device_id]
WHERE da.[attribute_name] IN
(
'attrib #1'
, 'attrib #2'
, 'attrib #3'
, 'attrib #4'
)
)
SELECT [device_display_name]
, [device_group_name]
, [device_location]
, [attribute_name]
, [attribute_value]
FROM cte_AttributedDevices
ORDER BY [device_name] ASC, [attribute_name] ASC ;
Result:
device_display_name | device_group_name | device_location | attribute_name | attribute_value
---------------------------------------------------------------------------------------
Device 1 Group 1A Location A attrib #1 0.10
Device 1 Group 1A Location A attrib #2 0.02
Device 1 Group 1A Location A attrib #3 0.07
Device 1 Group 1A Location A attrib #4 0.02
Device 2 Group 1A Location A attrib #1 0.16
Device 2 Group 1A Location A attrib #2 0.05
Device 2 Group 1A Location A attrib #3 0.12
Device 2 Group 1A Location A attrib #4 0.04
Device 3 Group 1A Location B attrib #1 0.15
Device 3 Group 1A Location B attrib #2 0.05
Device 3 Group 1A Location B attrib #3 0.07
Device 3 Group 1A Location B attrib #4 0.06
Device 4 Group 1A Location B attrib #1 0.10
Device 4 Group 1A Location B attrib #2 0.03
Device 4 Group 1A Location B attrib #3 0.07
Device 4 Group 1A Location B attrib #4 0.03
But when I execute a PIVOT query (pivoting the attribute_name column), with the same CTE base, against the same dataset, all attribute values are NULLed.
Query:
/* pivot query */
USE [DeviceDatabase] ;
GO
DECLARE @PrinterGroup AS VARCHAR(50) ;
SET @PrinterGroup = 'Group 1A' ;
WITH cte_GroupedDevices AS
(
SELECT d.[id] AS [device_id]
, d.[device_name]
, d.[device_display_name]
, d.[device_location]
, dg.[device_group_name]
FROM dbo.Devices AS d
INNER JOIN dbo.DeviceDeviceGroups AS ddg
ON d.[id] = ddg.[device_id]
INNER JOIN dbo.DeviceGroups AS dg
ON ddg.[group_id] = dg.[id]
WHERE dg.[device_group_name] = @PrinterGroup
)
, cte_AttributedDevices AS
(
SELECT gd.[device_name]
, gd.[device_display_name]
, gd.[device_group_name]
, gd.[device_location]
, da.[attribute_name]
, CAST ( da.[attribute_value] AS DECIMAL (5,2) ) AS [attribute_value]
FROM cte_GroupedDevices AS gd
INNER JOIN dbo.DeviceAttributes AS da
ON gd.[device_id] = da.[device_id]
WHERE da.[attribute_name] IN
(
'attrib #1'
, 'attrib #2'
, 'attrib #3'
, 'attrib #4'
)
)
SELECT [device_display_name]
, [device_group_name]
, [device_location]
, [attrib_1]
, [attrib_2]
, [attrib_3]
, [attrib_4]
FROM cte_AttributedDevices
PIVOT
(
MIN ( [attribute_value] )
FOR [attribute_name] IN
(
[attrib_1]
, [attrib_2]
, [attrib_3]
, [attrib_4]
)
) AS pvt
ORDER BY [device_name] ASC ;
Result:
device_display_name | device_group_name | device_location | attrib_1 | attrib_2 | attrib_3 | attrib_4
------------------------------------------------------------------------------------------------------------
Device 1 Group 1A Location A NULL NULL NULL NULL
Device 2 Group 1A Location A NULL NULL NULL NULL
Device 3 Group 1A Location B NULL NULL NULL NULL
Device 4 Group 1A Location B NULL NULL NULL NULL
I have tried a number of different functions in the PIVOT segment of the query -- MIN, MAX, SUM, AVG -- all of which produce the same result.
I have tried dumping the data into a temp table -- one with a numeric data type on the attribute_value column -- but it produces the same result, both with the flat and the pivoted query.
And I have tried a NOT NULL constraint on the attribute_value column, both in the base table and on the temp table. Again with the same results.
What am I doing wrong?
CodePudding user response：
Here is a working example. Note: @YourResults is a substitute for your actual initial query.
I don't see any logic where you transform attrib #1 to attrib_1
Example
Declare @YourResults Table ([device_display_name] varchar(50),[device_group_name] varchar(50),[device_location] varchar(50),[attribute_name] varchar(50),[attribute_value] varchar(50))
Insert Into @YourResults Values
('Device 1','Group 1A','Location A','attrib #1',0.10)
,('Device 1','Group 1A','Location A','attrib #2',0.02)
,('Device 1','Group 1A','Location A','attrib #3',0.07)
,('Device 1','Group 1A','Location A','attrib #4',0.02)
,('Device 2','Group 1A','Location A','attrib #1',0.16)
,('Device 2','Group 1A','Location A','attrib #2',0.05)
,('Device 2','Group 1A','Location A','attrib #3',0.12)
,('Device 2','Group 1A','Location A','attrib #4',0.04)
,('Device 3','Group 1A','Location B','attrib #1',0.15)
,('Device 3','Group 1A','Location B','attrib #2',0.05)
,('Device 3','Group 1A','Location B','attrib #3',0.07)
,('Device 3','Group 1A','Location B','attrib #4',0.06)
,('Device 4','Group 1A','Location B','attrib #1',0.10)
,('Device 4','Group 1A','Location B','attrib #2',0.03)
,('Device 4','Group 1A','Location B','attrib #3',0.07)
,('Device 4','Group 1A','Location B','attrib #4',0.03)
Select *
From @YourResults
Pivot (min([attribute_value]) for [attribute_name] IN (
[attrib #1]
, [attrib #2]
, [attrib #3]
, [attrib #4]
) ) Pvt
Results
EDIT - If you want [attrib_1] You can specify the columns and assign the alias.
Select [device_display_name]
,[device_group_name]
,[device_location]
,[attrib_1] = [attrib #1]
,[attrib_2] = [attrib #2]
,[attrib_3] = [attrib #3]
,[attrib_4] = [attrib #4]
From @YourResults
Pivot (min([attribute_value]) for [attribute_name] IN (
[attrib #1]
, [attrib #2]
, [attrib #3]
, [attrib #4]
) ) Pvt
