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

I try to create a pivot table which only includes strings.

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

This is a simple version of my table:

这是我表的简单版本:

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' 

I want to use the PIVOT function to get the following Resultset

我想使用PIVOT函数来获取以下Resultset

Expectation

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

The problem I have is that the values for Level1 - Level3 only exist 1 time, while Level4 has 2 different values. So I'm always getting this Resultset:

我遇到的问题是Level1 - Level3的值只存在1次,而Level4有2个不同的值。所以我总是得到这个Resultset:

Reality

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

I'm using this code

我正在使用此代码

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

Any ideas how to fix this? I can not add more values for Level1 - Level3 in the source table.

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

I already tried to use RANK() instead of ROW_NUMBER() but it didnt work.

我已经尝试使用RANK()而不是ROW_NUMBER(),但它没有用。

Thanks for your help.

谢谢你的帮助。

1 个解决方案

#1

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;