Dynamic Query

month wise production

Format that I want

item codenameJulyAugSepOctNovDecjan

1002pvc resin 3020115060140
3501SWR pipe566045801002020




I create crosstab procedure as follows

create procedure up_CrossTab (@SelectStatement varchar(1000),
@PivotColumn varchar(100),
@Summary varchar(100),
@GroupbyField varchar(100),
@OtherColumns varchar(100) = Null)
AS
/*
Inputs are any 1000 character or less valid SELECT sql statement,
the name of the column to pivot (transform to rows), the instructions to summarize the data, the field you want to group on, and other fields returned as output. 1
*/
set nocount on
set ansi_warnings off

declare @Values varchar(8000);
set @Values = '';

set @OtherColumns= isNull(', ' + @OtherColumns,'')
/*
An 8000 varchar variable called @values is created to hold the [potentially filtered] values in the pivot column. @Values is initiated to an empty string. Then, a temporary table is created to hold each unique value. After the table is created, its rows are loaded into the variable @values. It's usefullness completed, the temporary table is destroyed. 2
*/
create table #temp (Tempfield varchar(100))

insert into #temp
exec ('select distinct convert(varchar(100),' + @PivotColumn + ') as Tempfield FROM (' + @SelectStatement + ') A')

select @Values = @Values + ', ' +
replace(replace(@Summary,'(','(CASE WHEN ' + @PivotColumn + '=''' +
Tempfield + ''' THEN '),')[', ' END) as [' + Tempfield )
from #Temp
order by Tempfield

drop table #Temp
/*
Finally, a dynamic sql select statement is executed which takes the GroupByField, and OtherColumns, passed into the procedure, and each of the Values from the Pivot Column from the passed in SELECT statement . 3
*/
exec ( 'select ' + @GroupbyField + @OtherColumns + @Values +
' from (' + @SelectStatement + ') A GROUP BY ' + @GroupbyField)

set nocount off
set ansi_warnings on
GO


And then my sql query is as like


EXEC up_CrossTab 'SELECT ProdId, GrnDate,Quantity FROM inteacc..IcGrnD IcGrnD
INNER JOIN inteacc..IcProduct IcProduct ON (IcGrnD.ProdId=IcProduct.ProdId) ',
'Year(GrnDate)', 'sum(Quantity)[]', 'ProdId'




error occurring


ambiguous column name ‘ProdId’




But when I compile this query

EXEC up_CrossTab 'SELECT grnNo,GrnDate,Quantity FROM inteacc..IcGrnD IcGrnD
INNER JOIN inteacc..IcProduct IcProduct ON (IcGrnD.ProdId=IcProduct.ProdId) ',
'Month(GrnDate)', 'sum(Quantity)[]','GrnNo'


Output


GrnNo12249
1220NullNull20
2Null20Null10
3NullNull300Null
4NullNull10Null



I could not understand the error.

What will I do to get the format I want?


shohan