How to replace null values ​​in a pivot table?

Question

I am trying to create a pivot table that includes only rows.

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 results

Expectation

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

The problem is that the values ​​for Level1 - Level3 exist only once, while Level4 has 2 different values. Therefore, I always get this result set:

Reality

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

I am 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 cannot add more values ​​for Level1 - Level3 to the original table.

I have already tried using RANK () instead of ROW_NUMBER (), but it did not work.

Thank you 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;