Dynamic Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Express Edition and Compact Edition (2005)

Dynamic Query

Author

Topic

shohan_db
Starting Member

36 Posts

Posted - 2006-10-07 : 01:25:55

month wise production

Format that I want

item code name July Aug Sep Oct Nov Dec jan

1002 pvc resin 30 20 11 50 60 14 0
3501 SWR pipe 56 60 45 80 100 20 20

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

GrnNo 12 2 4 9
1 220 Null Null 20
2 Null 20 Null 10
3 Null Null 300 Null
4 Null Null 10 Null

I could not understand the error.

What will I do to get the format I want?

shohan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-07 : 01:27:03

duplicate post http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73186

Post on one of the forum will no. Please please please do not cross post.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources