I realize that I’m mixing real names with examples. I will show the most important columns.

Please take this as the example:

DataType of the Table to be PIVOT

#TestTable

ClientIdint

Advarchar(10)

TagIDint

Namevarchar(10)

Totalint

This will be the data in the Table

ClientIdAdTagIdNameTotal

1D147American1000

1D247American500

1D347American300

1D448Hispanic1000

1D548Hispanic200

This is the result when I pivot the table

ClientIdTagIdNameD1D2D3D4D5

147American1000500300NULLNULL

148HispanicNULLNULLNULL1000200

You can copy and paste this sample in Sql to create the Scenario:

create table #TestTable

(

ClientIdint

,Advarchar(10)

,TagIDint

,[Name]varchar(10)

,Totalint

)

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)

Same question: How I convert the NULL to value 0 in the Result. When I add it in the SELECT * From, it doesnt reconize it.

Thank you