如何替换数据透视表中的NULL值？

Nc_Sr 发表于 Dev

Nc_Sr

我尝试创建仅包含字符串的数据透视表。

这是我表的简单版本：

CREATE TABLE SecurityGroup (GroupName VARCHAR(20), SecLevel VARCHAR(20),       Power VARCHAR(20))
INSERT INTO SecurityGroup 
SELECT 'GroupA','Level1','read'
UNION
SELECT 'GroupA','Level2','write'  
UNION
SELECT 'GroupA','Level3','read'  
UNION
SELECT 'GroupA','Level4','read'  
UNION
SELECT 'GroupA','Level4','write'

我想使用PIVOT函数获取以下结果集

期待

GroupName    Level1    Level2    Level3    Level4
GroupA       read      write     read      read
GroupA       read      write     read      write

我的问题是，级别1-级别3的值仅存在1次，而级别4具有2个不同的值。所以我总是得到这个结果集：

现实

GroupName    Level1    Level2    Level3    Level4
GroupA       read      write     read      read
GroupA       NULL      NULL      NULL      write

我正在使用此代码

SELECT 
 [GroupName],
 [Level1],
 [Level2],
 [Level3],
 [Level4]
FROM 
 (SELECT
 [GroupName],
 [SecLevel], 
 [Power],
 ROW_NUMBER() OVER(PARTITION BY [GroupName], [SecLevel] ORDER BY [Power]) AS    rn
FROM [SecurityGroup]) AS SourceTable
PIVOT
 (MAX([Power])
  FOR [SecLevel] 
  IN ([Level1], [Level2], [Level3], [Level4])
 ) AS PivotTable

任何想法如何解决这一问题？我无法在源表中为Level1-Level3添加更多值。

我已经尝试使用RANK（）而不是ROW_NUMBER（），但是它没有用。

谢谢你的帮助。

布鲁斯·邓维迪

SELECT 
 [GroupName],
 MAX([Level1]) OVER (PARTITION BY [GroupName]) [Level1],
 MAX([Level2]) OVER (PARTITION BY [GroupName]) [Level2],
 MAX([Level3]) OVER (PARTITION BY [GroupName]) [Level3],
 [Level4]
FROM 
 (SELECT
 [GroupName],
 [SecLevel], 
 [Power],
 ROW_NUMBER() OVER(PARTITION BY [GroupName], [SecLevel] ORDER BY [Power]) AS    rn
FROM [SecurityGroup]) AS SourceTable
PIVOT
 (MAX([Power])
  FOR [SecLevel] 
  IN ([Level1], [Level2], [Level3], [Level4])
 ) AS PivotTable;