Creating Dynamic Pivot Table using SQL

 In this post, I want to share SQL tips on how to create dynamic pivot table in SQL.

The columns in the pivot table is not fixed in this case. Hence, there is a need to use dynamic SQL to achieve this.

In this example, we will be forming a pivot table containing spending by outlet from spending data.

SQL Query:

--prepare data source
create table #TmpSpending
(
	MemberID nvarchar(100),
	Outlet nvarchar(100),
	TransactDate date,
	Spending numeric(18,4)
)

insert into #TmpSpending values ('M0001', 'Outlet A', '2021/01/05', 100)
insert into #TmpSpending values ('M0001', 'Outlet B', '2021/01/15', 200)
insert into #TmpSpending values ('M0002', 'Outlet A', '2021/02/09', 300)
insert into #TmpSpending values ('M0003', 'Outlet B', '2021/01/10', 400)
insert into #TmpSpending values ('M0003', 'Outlet B', '2021/02/15', 500)

select * from #tmpspending

--temporary table for output
create table #tmpOutput (
	Outlet nvarchar(100)
)

--initial insert into #tmpOutput
insert into #tmpOutput
select distinct outlet
from #TmpSpending
order by outlet

--store the list of [month year] first
select distinct [Year] = year(transactdate), 
	[Month] = month(transactdate),
	MonthYear = left(datename(month, transactdate), 3) + ' ' + convert(varchar, year(transactdate))
into #tmpDates
from #tmpspending

--construct the table with dynamic columns using cursor
declare @year int, @month int
declare @monthyear nvarchar(8)
declare @sql nvarchar(max) = '' --dynamic sql for columns addition

declare cur1 cursor for
select [year], [month], MonthYear
from #tmpDates
order by [year], [month]

open cur1

fetch next from cur1
into @year, @month, @monthyear

while @@FETCH_STATUS = 0
begin
	
	--add column to #tmpOutput
	set @sql = 'alter table #tmpOutput add [' + @monthyear + '] numeric(18,4)'
	exec (@sql)

	--update #tmpOutput
	set @sql = 'update o '
	set @sql += 'set [' + @monthyear + '] = s.spending '
	set @sql += 'from #tmpOutput o '
	set @sql += 'inner join (	select outlet, spending = sum(spending) '
	set @sql += '				from #tmpspending '
	set @sql += '				where year(transactdate) = ' + convert(varchar, @year) + ' '
	set @sql += '				and month(transactdate) = ' + convert(varchar, @month) + ' '
	set @sql += '				group by outlet '
	set @sql += ') s on s.outlet = o.outlet '
	exec(@sql)

	--fetch next record
	fetch next from cur1
	into @year, @month, @monthyear
end

close cur1
deallocate cur1

select * from #tmpOutput

drop table #tmpDates
drop table #TmpSpending
drop table #tmpOutput

First, we prepare the data source which looks like this.

Afterwards, we use cursor (or you can use while loop) to create additional columns on the table on the fly, to form up the pivot table.

This is the end result of the pivot table.

Happy querying! :)