-- dynamic sql

/*

select *

into #Product

from (

select 1 ID, 'Mango' Name union all

select 2 ID, 'Orange' Name union all

select 3 ID, 'Apple' Name ) r

select *

into #ProductDetails

from (

select 1 ID, 1 Type ,'abcd' Code union all

select 1 ID, 2 Type ,'efgh' Code union all

select 1 ID, 3 Type ,'sdkl' Code union all

select 2 ID, 1 Type ,'mkcl' Code union all

select 3 ID, 50 Type ,'fgfdd' Code

) r

*/

declare @sql varchar(2000)

set @sql = 'select a.id , b.Name '

select @sql = @sql + ', max( case when a.type = ' + convert(varchar, (number + 1 ) ) + ' then a.code else '''' end) [code' + + convert(varchar, (number + 1 ) ) + ']'

from master.dbo.spt_values where type = 'P'

and number < ( select MAX(Type) from #ProductDetails )

select @sql = @sql + ' from #ProductDetails a , #Product b where a.id = b.id group by a.id , b.Name'

exec(@sql )

..