the script create the table and load it, just run.
A Possible Alternative to SQL UNPIVOT
Understand how to use the SQL UNPIVOT statement and then an equivalent using XML and XPath Querying in SQL Server.
2017-01-16
4,906 reads
the script create the table and load it, just run.
------------------------------------------------------------------------------------------ --------- 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