--Just a convenient way to rename a column --without the usual warning message create procedure sk_rename_column ( @table sysname, @oldname sysname, @newname sysname ) as DBCC RENAMECOLUMN(@table,@oldname,@newname) go set nocount on create table Sales ( AccountCode char(5), Category int, Amount decimal(8,2) ) --Populate table with sample data insert into Sales select customerID, EmployeeID, sum(Freight) from Northwind.dbo.orders group by customerID, EmployeeID create unique clustered index Sales_AC_C on Sales(AccountCode,Category) --Create table to hold data column names and positions select A.Category, count(distinct B.Category) AS Position into #columns from Sales A join Sales B on A.Category >= B.Category group by A.Category create unique clustered index #columns_P on #columns(Position) create unique index #columns_C on #columns(Category) --Generate first column of Pivot table select distinct AccountCode into Pivot from Sales --Find number of data columns to be added to Pivot table declare @datacols int select @datacols = max(Position) from #columns --Add data columns one by one in the correct order declare @i int set @i = 0 while @i < @datacols begin set @i = @i + 1 --Add next data column to Pivot table select P.*, isnull(( select Amount from Sales S join #columns C on C.Position = @i and C.Category = S.Category where P.AccountCode = S.AccountCode),0) AS X into PivotAugmented from Pivot P --Name new data column correctly declare @c sysname select @c = Category from #columns where Position = @i exec sk_rename_column '[dbo].[PivotAugmented]', 'X', @c --Replace Pivot table with new table drop table Pivot select * into Pivot from PivotAugmented drop table PivotAugmented end select * from Pivot go drop table Pivot drop table #columns drop table Sales drop procedure sk_rename_column