Id	TagId	Data	Column1	Column2
1	47	Data1	NULL	37661
1	48	Data2	5689	NULL
1	49	Data3	NULL	NULL

 SET @SQL = 'SELECT *  FROM 
	  (Select Id,Id,Data, Columns, TOTAL from #SpillReport)  SourceData
	PIVOT
	  (min(Total) for Columns in ('+@Columns+')) pivottable 
	Order by Id, TagId
exec(@sql)

   declare @columns varchar(max)
   declare @sql nvarchar(max)
 	SET @Columns = substring((select distinct ',['+col1+']' from #Table group by Col1 for xml path('')),2,8000)
	SET @SQL = 'SELECT *  FROM 
	  (Select id, ZId,TagId,Name, Col1, Total from #SpillReport )  SourceData
	PIVOT
	  (min(Total) for Col1 in ('+@Columns+')) pivottable 
	Order by ZoneId, TagId '
	exec(@sql)

#TestTable
ClientId	int
Ad	varchar(10)
TagID	int
Name	varchar(10)
Total	int

ClientId	Ad	TagId		Name		Total
1		D1	47		American	1000
1		D2	47		American	500
1		D3	47		American	300
1		D4	48		Hispanic	1000
1		D5	48		Hispanic	200

ClientId	TagId	Name	D1	D2	D3	D4	D5
1	47	American	1000	500	300	NULL	NULL
1	48	Hispanic	NULL	NULL	NULL	1000	200

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(max)
   declare @sql nvarchar(max)
 	SET @Columns = substring((select distinct ',['+Ad+']' from #TestTable group by Ad for xml path('')),2,8000)
	SET @SQL = 'SELECT *  FROM 
	  (Select Clientid, TagId, [Name], ad, Total from #TestTable )  SourceData
	PIVOT
	  (min(Total) for ad in ('+@Columns+')) pivottable 
	Order by ClientId, TagId '
	exec(@sql)

    DECLARE @columns VARCHAR(8000)
   DECLARE @columns2 VARCHAR(8000)
   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 @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)
/* result set:
Clientid	TagId	Name	D1	D2	D3	D4	D5
1	47	American	1000	500	300	0	0
1	48	Hispanic	0	0	0	1000	200
*/