hi,
try it with below,it might be sol your problem.
*****************Main *********************start
DECLARE @SEL NVARCHAR(MAX)
SET @SEL='select
0 unit_id from Unit_table
group by unit_name
'
exec dynamic_pivot @SEL
,'unit_name'
,'AVG(unit_id )'
print @SEL
select * into #temp1 from ##tmp213
select * from #temp1
DROP TABLE ##tmp213
DROP TABLE #temp1
*****************Main *********************End
First Execute this
CREATE procedure dynamic_pivot
(
@select varchar(2000),
@PivotCol varchar(100),
@Summaries varchar(100)
-- GET DATA IN PIVOT COLUMN
) as
declare @pivot varchar(max), @sql varchar(max)
select @select =replace(@select,'select ','select '+@PivotCol+' as
pivot_col,')
create table #pivot_columns (pivot_column varchar(100))
Select @sql='select distinct pivot_col from ('+@select+') as t'
insert into #pivot_columns
exec(@sql)
select @pivot=coalesce(@pivot+',','')+'['+pivot_column+']'from
#pivot_columns
select @sql=
'
select * INTO ##tmp213 from
(
'+@select+'
) as t
pivot
(
'+@Summaries+' for pivot_col in ('+@pivot+')
) as p
'
exec(@sql)
DROP TABLE #pivot_columns