Need to show NULL as a value of missing attributes in a separate row

Question

I have a table in MSSQL 17 like below:

Attribute   Value   Code
Country     Canada  AA
Source      EFT     AA
Manager     Ahmad   AA
Source      EFT     BB
Manager     Mike    BB
Country     Brazil  CC
Source      Cash    CC

I need all the codes have the same number of rows, showing the same attributes where the value should be NULL if it does not exist in the table. The output I am looking for:

Attribute   Value   Code
Country     Canada  AA
Source      EFT     AA
Manager     Ahmad   AA
Country     NULL    BB
Source      EFT     BB
Manager     Mike    BB
Country     Brazil  CC
Source      Cash    CC
Manager     NULL    CC

Can you please help me? Apprecited!

Answer 1

Use a cross join to generate the rows and then a left join to bring in the existing values:

select a.attribute, t.value, c.code
from (select distinct code from t) c cross join
     (select distinct attribute from t) a left join
     t
     on c.code = t.code and a.attribute = t.attribute