create table #TestTable

(

ClientId int

,Ad varchar(10)

,TagID int

,[Name] varchar(10)

,Total int

)

Insert #TestTable values (1 , 'D1', 47, 'American', 1000)

Insert #TestTable values (1 , 'D2', 47, 'American', 500)

Insert #TestTable values (1 , 'D3', 47, 'American', 300)

Insert #TestTable values (1 , 'D4', 48, 'Hispanic', 1000)

Insert #TestTable values (1 , 'D5', 48, 'Hispanic', 200)

DECLARE @columns VARCHAR(8000)

DECLARE @columns2 VARCHAR(8000)

declare @TotalXint

DECLARE @sql NVARCHAR(MAX)

SET @Columns = SUBSTRING((SELECT DISTINCT ',['+Ad+']' FROM #TestTable GROUP BY Ad FOR XML PATH('')),2,8000)

SET @Columns2 = SUBSTRING((SELECT DISTINCT ',ISNULL(['+Ad+'],0) AS ['+Ad+']' FROM #TestTable GROUP BY Ad FOR XML PATH('')),2,8000)

set @TotalX = 3

SET @SQL ='SELECT Clientid, TagId, [Name],' + @Columns2 + '

FROM

(Select Clientid, TagId, [Name], ad, Total from #TestTable ) SourceData

PIVOT

(min(Total) for ad in ('+@Columns+')) pivottable

Order by ClientId, TagId '

--EXEC(@sql)

--drop table #TestTable

CREATE TABLE #Temp1

(

clientid int,

tagid int,

tname varchar(max),

d1 int,

d2 int,

d3 int,

d4 int,

d5 int

)

insert into #temp1

EXEC(@sql)

declare @counter varchar(25)

SET @Counter = 1

DECLARE @ColumnCreator NVARCHAR(MAX)

WHILE@Counter <= @TotalX

BEGIN

SET @ColumnCreator = 'ALTER TABLE#Temp1 ADD DTotal'+ @Counter + ' DECIMAL(18,2)'

EXEC(@ColumnCreator)

SET@Counter = @Counter + 1

END

DECLARE @TotalCreator NVARCHAR(MAX)

declare @i varchar(5)

set @i = 1

declare @i1 varchar(5)

set @i1 = 2

SET @TotalCreator ='update #temp1 set dtotal'+@i+' = d'+ @i + '+ d'+ @i1

exec(@TotalCreator)

set @i = @i+1

set @i1 = @i1+1

SET @TotalCreator ='update #temp1 set dtotal'+@i+' = d'+ @i + '+ d'+ @i1

exec(@TotalCreator)

set @i = @i+1

set @i1 = @i1+1

SET @TotalCreator ='update #temp1 set dtotal'+@i+' = d'+ @i + '+ d'+ @i1

exec(@TotalCreator)

set @i = @i+1

set @i1 = @i1+1

select * from #temp1

drop table #TestTable

drop table #Temp1