how to replace NULL values in pivot table?

Question

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

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:

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.

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

Thanks for your help.

Answer 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;