CREATE TABLE Employees (
Employee_Id INT,
[Year] INT,
[Quarter] INT,
Income MONEY)
INSERT Employees VALUES(1, 2022, 1, 100000)
INSERT Employees VALUES(1, 2022, 2, 100000)
INSERT Employees VALUES(1, 2022, 3, 100000)
INSERT Employees VALUES(1, 2022, 4, 150000)
INSERT Employees VALUES(1, 2023, 1, 100000)
INSERT Employees VALUES(2, 2022, 2, 90000)
INSERT Employees VALUES(2, 2022, 3, 95000)
INSERT Employees VALUES(2, 2022, 4, 90000)
INSERT Employees VALUES(2, 2023, 1, 120000)
INSERT Employees VALUES(3, 2023, 1, 200000);
SELECT Employee_Id, [2022], [2023]
FROM
(
SELECT Employee_Id, [Year], Income FROM Employees
) AS source_query
PIVOT
(
SUM(Income) FOR [Year] IN ([2022], [2023])
) AS pivoted_table;
CREATE TABLE Pivoted_Employees(
Employee_Id INT,
[2022] MONEY,
[2023] MONEY
)
INSERT Pivoted_Employees VALUES(1, 450000, 100000)
INSERT Pivoted_Employees VALUES(2, 450000, 120000)
INSERT Pivoted_Employees VALUES(3, NULL, 200000)
select Employee_Id, [Year], Income
from (
select Employee_Id, [2022], [2023]
from Pivoted_Employees
) as pivoted_employees
unpivot(
Income for [Year] in ([2022], [2023])
) as unpivoted_employees;