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! :)

Read More

Activate Tableau Server Offline

If you are doing Tableau server license activation offline, it may be a bit problematic, and confusing.

There is already an article published by Tableau showing how to do Tableau server license activation offline. However, the last part about how to process the activation.tlf is not mentioned.

Hence, I am creating some guide on how to do that.

After you upload the offline.tlf file on Tableau site, and get the activation.tlf back, you need to:

1. Place the activation.tlf file somewhere in the server where you have your Tableau server installed. e.g. D:\activation.tlf

2. Open command prompt as Administrator from Start menu.

3. Go to the Tableau server directory where tabadmin.bat is located. Usually it is located at C:\Program Files\Tableau\Tableau Server\<version>\bin.

cd\Program Files\Tableau\Tableau Server\<version>\bin

4. Use this command to activate using the activation.tlf file:

tabadmin activate -tlf D:\activation.tlf

5. That's all, folks.

Read More

Using Macro in Notepad++

 Sometimes, you need to bulk-edit text with the same patterns. However, you can't simply use text editor to find and replace the content. In this case, you can try using macro in Notepad++ to achieve that.

This is a simple video on how you can do that using Notepad++:

Read More