我有一個設備資料庫，其中包含有關設備的資料，包括名稱、位置以及一些數字和字串屬性。我正在嘗試執行查詢，僅將具有數值的那些屬性拉入資料透視表。
當我平淡地查詢資料時，資料看起來不錯。但是當我PIVOT在同一個資料集上運行查詢時，所有結果都是NULL.
設定：
/* 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
令人討厭的是，該attribute_value列是字串而不是數字，但并非所有屬性本質上都是數字。（這是供應商的架構。）
當我對表執行平面查詢（使用 CTE）時，我得到了一整套屬性值。
詢問：
/* 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 ;
結果：
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
但是，當我使用相同的 CTE 基礎針對相同的資料集執行PIVOT查詢（旋轉attribute_name列）時，所有屬性值都為 NULL。
詢問：
/* 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 ;
結果：
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
我PIVOT在查詢段中嘗試了許多不同的函式-- MIN, MAX, SUM, AVG-- 所有這些都會產生相同的結果。
我曾嘗試將資料轉儲到一個臨時表中——一個在attribute_value列上具有數字資料型別的表——但它產生相同的結果，無論是平面查詢還是透視查詢。
我已經在基表和臨時表中NOT NULL對attribute_value列進行了約束。同樣的結果。
我究竟做錯了什么？
uj5u.com熱心網友回復：
這是一個作業示例。注意：@YourResults 是您實際初始查詢的替代品。
我沒有看到你轉換attrib #1到的任何邏輯attrib_1
例子
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
結果
編輯 - 如果你想要 [attrib_1]你可以指定列并分配別名。
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/369109.html
標籤：sql-server 查询语句 枢