By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test(
id int null,
[month] [varchar](255) NULL,
[year] [float] NULL,
CA_resp [float] NULL,
CA_spen [money] NULL,
TX_resp [float] NULL,
TX_spen [money] NULL,
PA_resp [float] NULL,
PA_spen [money] NULL
)
INSERT into test
values(1, 'jan', 2022, 222, 450, 111, 450, 444, 550)
1 rows affected
select t.id, t.month, t.year, v.market, v.CA_resp as resp, v.CA_spen as spen
from test t
cross join (select 'CA' as market, CA_resp, CA_spen from test
union
select 'TX' as market, TX_resp, TX_spen from test
union
select 'PA' as market, PA_resp, PA_spen from test) v
| id | month | year | market | resp | spen |
|---|---|---|---|---|---|
| 1 | jan | 2022 | CA | 222 | 450.0000 |
| 1 | jan | 2022 | PA | 444 | 550.0000 |
| 1 | jan | 2022 | TX | 111 | 450.0000 |
select id, month, year, 'CA' as market, CA_resp as resp, CA_spen as spen from test
union
select id, month, year, 'TX' as market, TX_resp, TX_spen from test
union
select id, month, year, 'PA' as market, PA_resp, PA_spen from test
| id | month | year | market | resp | spen |
|---|---|---|---|---|---|
| 1 | jan | 2022 | CA | 222 | 450.0000 |
| 1 | jan | 2022 | PA | 444 | 550.0000 |
| 1 | jan | 2022 | TX | 111 | 450.0000 |