Generate Cross Tab data in SQL Server

Hi makumbi,

Refer below query.

SQL

CREATE TABLE Income(INCOMES INT, Dates DATETIME)
INSERT INTO Income VALUES(122500, '12/01/2017')
INSERT INTO Income VALUES(256000, '12/01/2018')
INSERT INTO Income VALUES(485555, '12/02/2019')
INSERT INTO Income VALUES(485585, '12/02/2021')
INSERT INTO Income VALUES(985585, '12/02/2023')

CREATE TABLE Expense(EXPENSES INT, Dates DATETIME)
INSERT INTO Expense VALUES(182500, '12/01/2017')
INSERT INTO Expense VALUES(253000, '12/01/2018')
INSERT INTO Expense VALUES(488555, '12/02/2019')
INSERT INTO Expense VALUES(488555, '12/02/2020')
INSERT INTO Expense VALUES(4888555, '12/02/2021')


DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX),@colsFormated AS NVARCHAR(MAX)

SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(YEAR(Dates))
            FROM (SELECT Dates FROM Income UNION SELECT Dates FROM Expense) p

            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

SET @query = ';WITH CTE AS
				(
					SELECT ''INCOMES'' AS Spent,INCOMES Amount,YEAR(Dates) Years 
					FROM Income
					UNION ALL 
					SELECT ''EXPENSES'',EXPENSES,YEAR(Dates) YEARS 
					FROM Expense
				)
				SELECT Spent AS YEARS, ' + @cols + ' from CTE
				pivot
				(
					MAX(Amount)
					for Years in (' + @cols + ')
				) p'

EXECUTE sp_executesql @query

Output