sql server - SQL convert some column names to row values and one column name row values to column name

Question

I have a table organized as follows:

Year |Account | Location| Measure1 |Measure2 |Measure3 

2020 |123a    |A       |100      |20%        |5
2020 |234b    |B       |75       |80%        |8
2020 |122c    |C       |80       |78%        |9

I want to create records as follows:

Year  |Account | Measure      |A    |B     |C

2020  |123a     |Measure1    |100   |      |
2020  |
2020  |234b     |Measure2    |      |80%   |
2020  |122c     |Measure3    |      |      |9

question from:https://stackoverflow.com/questions/65843036/sql-convert-some-column-names-to-row-values-and-one-column-name-row-values-to-co

Answer 1

This forum isn't for solving your programming tasks. But for the fun of it:

select Year, Account, 'Measure1' as Measure, Measure1 as A, null as B, null as C
from my_table
where Location = 'A'

union

select Year, Account, 'Measure2' as Measure, Measure2 as A, null as B, null as C
from my_table
where Location = 'A'

...

union

select Year, Account, 'Measure1' as Measure, null as A, Measure1 as B, null as C
from my_table
where Location = 'B'

...