Dynamic Columns with PIVOT Operator

------------------------------------------------------------------------------------------
--------- Load Data
------------------------------------------------------------------------------------------
create table #tmpData (ProductId int not null,
TypeId int not null,
RegionId int not null,
Value money NOT NULL
)
set nocount on


declare @x int
declare @MaxTypes int
declare @MaxRegions int


set @x = 1

--------------------------------------------------
set @MaxTypes = 3 -------- Set the total Types
set @MaxRegions = 5 -------- Set the total Regions
--------------------------------------------------

while (@x <= 100) begin


insert #tmpData(ProductId, TypeId, RegionId, Value)
select @x,
convert(int, rand() * @MaxTypes + 1),
convert(int, rand() * @MaxRegions + 1), rand() * 1500

set @x = @x + 1


end

set nocount off

------------------------------------------------------------------------------------------
---------- Convert
-----------------------------------------------------------------------------------------

--select ProductId, TypeId, RegionId, Value
--from #tmpData

select distinct '[' + right('0' + ltrim(str(RegionId)), 2) + '],' as 'Field'
into #tmpFields
from #tmpData
order by '[' + right('0' + ltrim(str(RegionId)), 2) + '],'

declare @string varchar(max)
declare @SQL varchar(max)

select @string = isnull(@string, '') + Field
from #tmpFields

set @String = left(@String, len(@String) -1)

select @SQL = 'select TypeId, ' + @String + '
'

select @SQL = @SQL + 'from 
'

select @SQL = @SQL + ' (select TypeId, RegionId, Value

from #tmpData) as t
pivot
(sum(Value) for RegionId in (' + @String + ')) as pvt
'

-- select @SQL

print 'top 10 Rows...'
select top 10 *
from #tmpData

exec (@SQL)

select sum(value) as 'Type 1, Region 3'
from #tmpData
where typeId = 1 and RegionID = 3

drop table #tmpFields
drop table #tmpData