please help - 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

General SQL Server Forums

New to SQL Server Programming

please help

Author

Topic

shohan_db
Starting Member

36 Posts

Posted - 2006-10-07 : 01:18:37

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:23:42

you can take a look here http://www.sqlteam.com/item.asp?ItemID=2955

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-07 : 01:34:18

quote:


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’

The ProdId in @GroupbyField. As ProdID existed in table IcGrnD andIcProduct, you need to prefix the column with the table name

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)[]', 'IcProduct.ProdId'

Kristen
Test

22859 Posts

Posted - 2006-10-07 : 01:43:47

When you posted here a month ago you were asked not to cross post and had all your duplicate posts locked - you've posted your two questions today 8 times - why are you doing it again? You will just annoy everyone and get fewer people offering suggestions.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources