Visual Basic - Stored Procedure for Crosstab and Calling from VB

Author

Stored Procedure for Crosstab and Calling from VB

Masriaty

2004-03-30, 3:30 am

Dear Developers dex Member,

I'm writing a VB program using MS SQL Server 7 as my database. Right now
I'm developing Cash Flow module. This module needs to use STORED
PROCEDURE for creating crosstab query for its detail. Eventhough I have
experience in writing VB coding and using SQL Server but this is my
first time using stored procedure.

Therefore I need some help from you guy about this. I need help on how
to create stored procedure for my detail table and also to call this
stored procedure.

My cash flow module contains 2 tables which are CashFlow and
CashFlowData. CashFlow table is header table while CashFlowData is
detail table. Both tables are link by ID which means CashFlowData
contain CashFlowID. Structure for both tables are like below. I only
list the important fields.

CASHFLOW table (header) :
Code_L1 [int]
w05_projcode [nvarchar]
ProcCategory [nvarchar]
ProcCode [nvarchar]
DataDate [datetime]
PaidToPeriod [int]
PeriodToBeForecast [int]
ScheduleStartDate [datetime]
ScheduleFinishDate [datetime]
PaidAmount [money]
HoldBackPercen] [float]
Delay [int]
Total [money]

CASHFLOWDATA table (detail) :
SummaryID [int]
w05_projcode [nvarchar] = not important
ProcCategory [nvarchar] = not important
ProcCode [nvarchar] = not important
Month [int]
Comment [ntext]
ForecastIncurredAmt [money]
TotalFCFuturePeriod [money]
CashFlowHdr [int] <== this is ID from CshFlow table

Table which is involved for crosstab query if CashFlowData table only. I
want Comment, ForecastIncurredAmt and
TotalFCFuturePeriod fields as row while Month field as column. Sample
data is like below :

SummaryID w05_projcode ProcCategory ProcCode Month Comment
ForecastIncurredAmt TotalFCFuturePeriod CashFlowHdr
1 ABC A AA 1
Rem A 1000.00 1000.00 1
2 ABC A AA 2
Rem A 1000.00 2000.00 1
3 ABC A AA 3
Rem A 1000.00 3000.00 1
4 ABC A AA 4
Rem A 1000.00 4000.00 1

After crosstab query it should display like below :

Month1 Month2 Month3 Month4 ........
Comment
ForecastIncurredAmt 1000.00 1000.00 1000.00 1000.00
ForecastIncurredAmt 1000.00 2000.00 3000.00 4000.00

Below is sample crosstab query which I found from Expert Exchange group.
This stored procedure is said is correct and run with no error. The
problem is I totally do not know what should I change so that the stored
procedure fits for my cash flow module. Also how to call this stored
procedure from my VB program.

Does anyone can help me on this. This is very urgent since I cannot
proceed my tasks if my crosstab query does not work. I really appreciate
helps from you guys out there.

Format: Sample from one of Expert Exchange Group member

CREATE PROCEDURE dbo.CashFlowCrossTab

@tablename varchar(255),
@crosscolumn varchar(255),
@crossrow varchar(255),
@crossvalue varchar(255)

AS

--work variable
Declare
@sql varchar(8000),
@colname varchar(255),
@i smallint,
@cols smallint,
@longest_col smallint,
@CrLF char(2)

--constant
Declare
@max_cols_in_table smallint,
@max_col_name_len smallint,
@max_statement_len smallint,
@sql7 bit,
@err_severity int

Set nocount On

Set @max_cols_in_table = 255
Set @max_statement_len = 8000
Set @max_col_name_len = 128
Set @err_severity = 11
Set @CrLF = char(13) + char(10)

--Check inputs
IF @tablename IS NULL OR @crosscolumn IS NULL OR @crossrow IS NULL OR
@crossvalue IS NULL BEGIN
Raiserror ('Missing Parameter(s)!',@err_severity,1)
Return 0
END

--Check for existence of the table
IF (NOT EXISTS(SELECT * FROM sysobjects WHERE name LIKE @tablename))
BEGIN
Raiserror('Table/View for crosstab not found!',@err_severity,1)
Return 0
END

--Don't check for columns because we may actually get an expression as
the column name
--prepare for future feature of checking database version to validate
input. Default to version 7

Set @sql7 = 1
IF (patindex('%SQL Server 7.%',@@version)=0) BEGIN
Set @sql7 = 0
END

--Extract all values from the rows of the attribute we want to use to
create the cross column.
--This table will contain one row for each column in the crosstab
Create table #crosscol(crosscolumn varchar(255))
Set @sql = ' Insert #crosscol Select Distinct ' + @crosscolumn +
' From ' + @tablename --+
--'Group By ' + @crosscolumn
--print @sql
Exec ( @sql)
Set @cols = @@rowcount

IF @cols > @max_cols_in_table BEGIN
Raiserror ('Exceeded maximum number of columns in
Cross-tab',@err_severity,1)
Return 0
END

ELSE BEGIN
IF @cols = 0 BEGIN
Raiserror ('Could not find values to use for columns in
Cross-tab',@err_severity,1)
Return 0
END

ELSE BEGIN
--Check if any of the data is too long to make it a name of a
column
SELECT

@longest_col = max(len(Convert(varchar(129),crosscolumn
))) FROM
#crosscol

IF @longest_col > @max_col_name_len BEGIN
Raiserror ('Value for colum name exceeds legal length of
column names',@err_severity,1)
Return 0
END

ELSE BEGIN
--All Validation OK, start building the dynamic sql
statement

Set @sql = ''
-- User tmp table rows to create the sql statement for
the crosstab.
--each row in the table will be a column in the cross-tab
Set @sql = 'SELECT ISNULL(Convert(varchar(255), ' +
@crossrow + '),"Undefined") AS ' +

@crossrow + ',' + @CrLF + Space(4)
--set @sql = 'SELECT' + @crossrow + ',' + char(13)

Declare cross_sql cursor for
SELECT crosscolumn
FROM #crosscol
ORDER BY crosscolumn

--print 'Sql cross statement: ' + @sql

Open cross_sql
Fetch next from cross_sql into @colname
--Use "@i" to check for the last column, We need to input
commas
--between columns, but not after the last column
Set @i = 0
--All Validation OK, start building the dynamic sql
statement

Set @sql = ''
-- User tmp table rows to create the sql statement for
the crosstab.
--each row in the table will be a column in the cross-tab
Set @sql = 'SELECT ISNULL(Convert(varchar(255), ' +
@crossrow + '),"Undefined") AS ' +

@crossrow + ',' + @CrLF + Space(4)
--set @sql = 'SELECT' + @crossrow + ',' + char(13)

Declare cross_sql cursor for
SELECT crosscolumn
FROM #crosscol
ORDER BY crosscolumn

--print 'Sql cross statement: ' + @sql

Open cross_sql
Fetch next from cross_sql into @colname
--Use "@i" to check for the last column, We need to input
commas
--between columns, but not after the last column
Set @i = 0
While @@FETCH_STATUS = 0 BEGIN
Set @i = @i + 1
Set @colname = ISNULL(@colname,'Undefined')

Set @crossvalue = ISNULL(@crossvalue,0)

Set @sql = @sql + '''' +
convert(varchar(128),@colname) +
''' = sum(case convert(varchar(128),' + @crosscolumn
+ ')'
+ char(13) + char(10) + space(8) +
' when ''' + @colname + ''' then ' +
@crossvalue + ' else 0 end) '

IF @i < @cols
Set @sql = @sql + ',' + @CrLF + Space(4)
ELSE
Set @sql = @sql + @CrLF

Fetch next from cross_sql into @colname
END

close cross_sql
deallocate cross_sql

Set @sql = @sql + ' from ' + @tablename + ' Group By ' +
@crossrow

IF LEN( @sql) >= @max_statement_len BEGIN
Raiserror ('Crosstab sql statement cannot exceed 7999
characters',@err_severity,1)
Return 0
END

Exec ( @sql)
Select 'Sql' = @sql
Set nocount off
Return 1

END
END
END

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Bonj

2004-03-30, 3:30 pm

Without digging too deep into it, it looks like the sample code you got
relies on parameters, i.e. the column names on the output (i.e. row values
of the crosstab column on the input) have to be defined prior to running the
query. If you want to do it dynamically, it involves some fairly complex
SQL - you have to have a cursor to loop through the values of the crosstab
column and add a new column to a (temporary) table for each one, then run
update statements using dynamic SQL.
Post as simple example as possible of some data tables you want to see how
to crosstab (invent test data - not your actual business data) and I'll post
back some example code that can dynamically crosstab it. I might try and
invent some myself to illustrate it when I've got more time, but I hope this
gets you on the right track for now.

"Masriaty" <masriaty_76@yahoo.com> wrote in message
news:%23n8awviFEHA.628@TK2MSFTNGP10.phx.gbl...
> Dear Developers dex Member,
>
> I'm writing a VB program using MS SQL Server 7 as my database. Right now
> I'm developing Cash Flow module. This module needs to use STORED
> PROCEDURE for creating crosstab query for its detail. Eventhough I have
> experience in writing VB coding and using SQL Server but this is my
> first time using stored procedure.
>
> Therefore I need some help from you guy about this. I need help on how
> to create stored procedure for my detail table and also to call this
> stored procedure.
>
> My cash flow module contains 2 tables which are CashFlow and
> CashFlowData. CashFlow table is header table while CashFlowData is
> detail table. Both tables are link by ID which means CashFlowData
> contain CashFlowID. Structure for both tables are like below. I only
> list the important fields.
>
> CASHFLOW table (header) :
> Code_L1 [int]
> w05_projcode [nvarchar]
> ProcCategory [nvarchar]
> ProcCode [nvarchar]
> DataDate [datetime]
> PaidToPeriod [int]
> PeriodToBeForecast [int]
> ScheduleStartDate [datetime]
> ScheduleFinishDate [datetime]
> PaidAmount [money]
> HoldBackPercen] [float]
> Delay [int]
> Total [money]
>
>
> CASHFLOWDATA table (detail) :
> SummaryID [int]
> w05_projcode [nvarchar] = not important
> ProcCategory [nvarchar] = not important
> ProcCode [nvarchar] = not important
> Month [int]
> Comment [ntext]
> ForecastIncurredAmt [money]
> TotalFCFuturePeriod [money]
> CashFlowHdr [int] <== this is ID from CshFlow table
>
> Table which is involved for crosstab query if CashFlowData table only. I
> want Comment, ForecastIncurredAmt and
> TotalFCFuturePeriod fields as row while Month field as column. Sample
> data is like below :
>
> SummaryID w05_projcode ProcCategory ProcCode Month Comment
> ForecastIncurredAmt TotalFCFuturePeriod CashFlowHdr
> 1 ABC A AA 1
> Rem A 1000.00 1000.00 1
> 2 ABC A AA 2
> Rem A 1000.00 2000.00 1
> 3 ABC A AA 3
> Rem A 1000.00 3000.00 1
> 4 ABC A AA 4
> Rem A 1000.00 4000.00 1
>
> After crosstab query it should display like below :
>
> Month1 Month2 Month3 Month4 ........
> Comment
> ForecastIncurredAmt 1000.00 1000.00 1000.00 1000.00
> ForecastIncurredAmt 1000.00 2000.00 3000.00 4000.00
>
> Below is sample crosstab query which I found from Expert Exchange group.
> This stored procedure is said is correct and run with no error. The
> problem is I totally do not know what should I change so that the stored
> procedure fits for my cash flow module. Also how to call this stored
> procedure from my VB program.
>
> Does anyone can help me on this. This is very urgent since I cannot
> proceed my tasks if my crosstab query does not work. I really appreciate
> helps from you guys out there.
>
>
> Format: Sample from one of Expert Exchange Group member
>
> CREATE PROCEDURE dbo.CashFlowCrossTab
>
> @tablename varchar(255),
> @crosscolumn varchar(255),
> @crossrow varchar(255),
> @crossvalue varchar(255)
>
> AS
>
> --work variable
> Declare
> @sql varchar(8000),
> @colname varchar(255),
> @i smallint,
> @cols smallint,
> @longest_col smallint,
> @CrLF char(2)
>
> --constant
> Declare
> @max_cols_in_table smallint,
> @max_col_name_len smallint,
> @max_statement_len smallint,
> @sql7 bit,
> @err_severity int
>
> Set nocount On
>
> Set @max_cols_in_table = 255
> Set @max_statement_len = 8000
> Set @max_col_name_len = 128
> Set @err_severity = 11
> Set @CrLF = char(13) + char(10)
>
> --Check inputs
> IF @tablename IS NULL OR @crosscolumn IS NULL OR @crossrow IS NULL OR
> @crossvalue IS NULL BEGIN
> Raiserror ('Missing Parameter(s)!',@err_severity,1)
> Return 0
> END
>
> --Check for existence of the table
> IF (NOT EXISTS(SELECT * FROM sysobjects WHERE name LIKE @tablename))
> BEGIN
> Raiserror('Table/View for crosstab not found!',@err_severity,1)
> Return 0
> END
>
> --Don't check for columns because we may actually get an expression as
> the column name
> --prepare for future feature of checking database version to validate
> input. Default to version 7
>
> Set @sql7 = 1
> IF (patindex('%SQL Server 7.%',@@version)=0) BEGIN
> Set @sql7 = 0
> END
>
> --Extract all values from the rows of the attribute we want to use to
> create the cross column.
> --This table will contain one row for each column in the crosstab
> Create table #crosscol(crosscolumn varchar(255))
> Set @sql = ' Insert #crosscol Select Distinct ' + @crosscolumn +
> ' From ' + @tablename --+
> --'Group By ' + @crosscolumn
> --print @sql
> Exec ( @sql)
> Set @cols = @@rowcount
>
> IF @cols > @max_cols_in_table BEGIN
> Raiserror ('Exceeded maximum number of columns in
> Cross-tab',@err_severity,1)
> Return 0
> END
>
> ELSE BEGIN
> IF @cols = 0 BEGIN
> Raiserror ('Could not find values to use for columns in
> Cross-tab',@err_severity,1)
> Return 0
> END
>
> ELSE BEGIN
> --Check if any of the data is too long to make it a name of a
> column
> SELECT
>
> @longest_col = max(len(Convert(varchar(129),crosscolumn
))) FROM
> #crosscol
>
> IF @longest_col > @max_col_name_len BEGIN
> Raiserror ('Value for colum name exceeds legal length of
> column names',@err_severity,1)
> Return 0
> END
>
> ELSE BEGIN
> --All Validation OK, start building the dynamic sql
> statement
>
> Set @sql = ''
> -- User tmp table rows to create the sql statement for
> the crosstab.
> --each row in the table will be a column in the cross-tab
> Set @sql = 'SELECT ISNULL(Convert(varchar(255), ' +
> @crossrow + '),"Undefined") AS ' +
>
> @crossrow + ',' + @CrLF + Space(4)
> --set @sql = 'SELECT' + @crossrow + ',' + char(13)
>
> Declare cross_sql cursor for
> SELECT crosscolumn
> FROM #crosscol
> ORDER BY crosscolumn
>
> --print 'Sql cross statement: ' + @sql
>
> Open cross_sql
> Fetch next from cross_sql into @colname
> --Use "@i" to check for the last column, We need to input
> commas
> --between columns, but not after the last column
> Set @i = 0
> --All Validation OK, start building the dynamic sql
> statement
>
> Set @sql = ''
> -- User tmp table rows to create the sql statement for
> the crosstab.
> --each row in the table will be a column in the cross-tab
> Set @sql = 'SELECT ISNULL(Convert(varchar(255), ' +
> @crossrow + '),"Undefined") AS ' +
>
> @crossrow + ',' + @CrLF + Space(4)
> --set @sql = 'SELECT' + @crossrow + ',' + char(13)
>
> Declare cross_sql cursor for
> SELECT crosscolumn
> FROM #crosscol
> ORDER BY crosscolumn
>
> --print 'Sql cross statement: ' + @sql
>
> Open cross_sql
> Fetch next from cross_sql into @colname
> --Use "@i" to check for the last column, We need to input
> commas
> --between columns, but not after the last column
> Set @i = 0
> While @@FETCH_STATUS = 0 BEGIN
> Set @i = @i + 1
> Set @colname = ISNULL(@colname,'Undefined')
>
>
> Set @crossvalue = ISNULL(@crossvalue,0)
>
> Set @sql = @sql + '''' +
> convert(varchar(128),@colname) +
> ''' = sum(case convert(varchar(128),' + @crosscolumn
> + ')'
> + char(13) + char(10) + space(8) +
> ' when ''' + @colname + ''' then ' +
> @crossvalue + ' else 0 end) '
>
> IF @i < @cols
> Set @sql = @sql + ',' + @CrLF + Space(4)
> ELSE
> Set @sql = @sql + @CrLF
>
> Fetch next from cross_sql into @colname
> END
>
> close cross_sql
> deallocate cross_sql
>
> Set @sql = @sql + ' from ' + @tablename + ' Group By ' +
> @crossrow
>
> IF LEN( @sql) >= @max_statement_len BEGIN
> Raiserror ('Crosstab sql statement cannot exceed 7999
> characters',@err_severity,1)
> Return 0
> END
>
> Exec ( @sql)
> Select 'Sql' = @sql
> Set nocount off
> Return 1
>
> END
> END
> END
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Bonj

2004-03-30, 5:30 pm

This code demonstrates the general principle of doing a crosstab in SQL:

create table dbo.input
(food varchar(255), month varchar(255), quantity int)

insert dbo.input(food, month, quantity)
values('Bread', 'Jan', 23)
insert dbo.input(food, month, quantity)
values('Cheese', 'Jan', 45)
insert dbo.input(food, month, quantity)
values('Chillies', 'Jan', 84)

insert dbo.input(food, month, quantity)
values('Bread', 'Feb', 34)
insert dbo.input(food, month, quantity)
values('Cheese', 'Feb', 25)
insert dbo.input(food, month, quantity)
values('Chillies', 'Feb', 16)

insert dbo.input(food, month, quantity)
values('Bread', 'Mar', 4)
insert dbo.input(food, month, quantity)
values('Cheese', 'Mar', 47)
insert dbo.input(food, month, quantity)
values('Chillies', 'Mar', 36)

create table dbo.output(food varchar(255))
insert dbo.output(food)
select distinct food from input
declare @month varchar(255) --month is the column we're swivelling
declare @sql varchar(255)
declare repstat cursor
local
fast_forward
read_only
for select distinct month from dbo.input
open repstat
fetch repstat into @month
while @@fetch_status=0
begin
select @sql='alter table dbo.output add ' + @month +
' varchar(255)'
execute(@sql)
select @sql='update dbo.output set ' + @month +
'=i.qtot from dbo.output o join ' +
'(select food, month, sum(quantity) qtot ' +
'from dbo.input where month=''' + @month +
''' group by food,month) i ' +
'on o.food=i.food'
execute(@sql)
fetch repstat into @month
end
close repstat
deallocate repstat
select * from dbo.output

--there you have it. Just run that and you'll see, you can then extrapolate
that to your real data.

"Masriaty" <masriaty_76@yahoo.com> wrote in message
news:%23n8awviFEHA.628@TK2MSFTNGP10.phx.gbl...
> Dear Developers dex Member,
>
> I'm writing a VB program using MS SQL Server 7 as my database. Right now
> I'm developing Cash Flow module. This module needs to use STORED
> PROCEDURE for creating crosstab query for its detail. Eventhough I have
> experience in writing VB coding and using SQL Server but this is my
> first time using stored procedure.
>
> Therefore I need some help from you guy about this. I need help on how
> to create stored procedure for my detail table and also to call this
> stored procedure.
>
> My cash flow module contains 2 tables which are CashFlow and
> CashFlowData. CashFlow table is header table while CashFlowData is
> detail table. Both tables are link by ID which means CashFlowData
> contain CashFlowID. Structure for both tables are like below. I only
> list the important fields.
>
> CASHFLOW table (header) :
> Code_L1 [int]
> w05_projcode [nvarchar]
> ProcCategory [nvarchar]
> ProcCode [nvarchar]
> DataDate [datetime]
> PaidToPeriod [int]
> PeriodToBeForecast [int]
> ScheduleStartDate [datetime]
> ScheduleFinishDate [datetime]
> PaidAmount [money]
> HoldBackPercen] [float]
> Delay [int]
> Total [money]
>
>
> CASHFLOWDATA table (detail) :
> SummaryID [int]
> w05_projcode [nvarchar] = not important
> ProcCategory [nvarchar] = not important
> ProcCode [nvarchar] = not important
> Month [int]
> Comment [ntext]
> ForecastIncurredAmt [money]
> TotalFCFuturePeriod [money]
> CashFlowHdr [int] <== this is ID from CshFlow table
>
> Table which is involved for crosstab query if CashFlowData table only. I
> want Comment, ForecastIncurredAmt and
> TotalFCFuturePeriod fields as row while Month field as column. Sample
> data is like below :
>
> SummaryID w05_projcode ProcCategory ProcCode Month Comment
> ForecastIncurredAmt TotalFCFuturePeriod CashFlowHdr
> 1 ABC A AA 1
> Rem A 1000.00 1000.00 1
> 2 ABC A AA 2
> Rem A 1000.00 2000.00 1
> 3 ABC A AA 3
> Rem A 1000.00 3000.00 1
> 4 ABC A AA 4
> Rem A 1000.00 4000.00 1
>
> After crosstab query it should display like below :
>
> Month1 Month2 Month3 Month4 ........
> Comment
> ForecastIncurredAmt 1000.00 1000.00 1000.00 1000.00
> ForecastIncurredAmt 1000.00 2000.00 3000.00 4000.00
>
> Below is sample crosstab query which I found from Expert Exchange group.
> This stored procedure is said is correct and run with no error. The
> problem is I totally do not know what should I change so that the stored
> procedure fits for my cash flow module. Also how to call this stored
> procedure from my VB program.
>
> Does anyone can help me on this. This is very urgent since I cannot
> proceed my tasks if my crosstab query does not work. I really appreciate
> helps from you guys out there.
>
>
> Format: Sample from one of Expert Exchange Group member
>
> CREATE PROCEDURE dbo.CashFlowCrossTab
>
> @tablename varchar(255),
> @crosscolumn varchar(255),
> @crossrow varchar(255),
> @crossvalue varchar(255)
>
> AS
>
> --work variable
> Declare
> @sql varchar(8000),
> @colname varchar(255),
> @i smallint,
> @cols smallint,
> @longest_col smallint,
> @CrLF char(2)
>
> --constant
> Declare
> @max_cols_in_table smallint,
> @max_col_name_len smallint,
> @max_statement_len smallint,
> @sql7 bit,
> @err_severity int
>
> Set nocount On
>
> Set @max_cols_in_table = 255
> Set @max_statement_len = 8000
> Set @max_col_name_len = 128
> Set @err_severity = 11
> Set @CrLF = char(13) + char(10)
>
> --Check inputs
> IF @tablename IS NULL OR @crosscolumn IS NULL OR @crossrow IS NULL OR
> @crossvalue IS NULL BEGIN
> Raiserror ('Missing Parameter(s)!',@err_severity,1)
> Return 0
> END
>
> --Check for existence of the table
> IF (NOT EXISTS(SELECT * FROM sysobjects WHERE name LIKE @tablename))
> BEGIN
> Raiserror('Table/View for crosstab not found!',@err_severity,1)
> Return 0
> END
>
> --Don't check for columns because we may actually get an expression as
> the column name
> --prepare for future feature of checking database version to validate
> input. Default to version 7
>
> Set @sql7 = 1
> IF (patindex('%SQL Server 7.%',@@version)=0) BEGIN
> Set @sql7 = 0
> END
>
> --Extract all values from the rows of the attribute we want to use to
> create the cross column.
> --This table will contain one row for each column in the crosstab
> Create table #crosscol(crosscolumn varchar(255))
> Set @sql = ' Insert #crosscol Select Distinct ' + @crosscolumn +
> ' From ' + @tablename --+
> --'Group By ' + @crosscolumn
> --print @sql
> Exec ( @sql)
> Set @cols = @@rowcount
>
> IF @cols > @max_cols_in_table BEGIN
> Raiserror ('Exceeded maximum number of columns in
> Cross-tab',@err_severity,1)
> Return 0
> END
>
> ELSE BEGIN
> IF @cols = 0 BEGIN
> Raiserror ('Could not find values to use for columns in
> Cross-tab',@err_severity,1)
> Return 0
> END
>
> ELSE BEGIN
> --Check if any of the data is too long to make it a name of a
> column
> SELECT
>
> @longest_col = max(len(Convert(varchar(129),crosscolumn
))) FROM
> #crosscol
>
> IF @longest_col > @max_col_name_len BEGIN
> Raiserror ('Value for colum name exceeds legal length of
> column names',@err_severity,1)
> Return 0
> END
>
> ELSE BEGIN
> --All Validation OK, start building the dynamic sql
> statement
>
> Set @sql = ''
> -- User tmp table rows to create the sql statement for
> the crosstab.
> --each row in the table will be a column in the cross-tab
> Set @sql = 'SELECT ISNULL(Convert(varchar(255), ' +
> @crossrow + '),"Undefined") AS ' +
>
> @crossrow + ',' + @CrLF + Space(4)
> --set @sql = 'SELECT' + @crossrow + ',' + char(13)
>
> Declare cross_sql cursor for
> SELECT crosscolumn
> FROM #crosscol
> ORDER BY crosscolumn
>
> --print 'Sql cross statement: ' + @sql
>
> Open cross_sql
> Fetch next from cross_sql into @colname
> --Use "@i" to check for the last column, We need to input
> commas
> --between columns, but not after the last column
> Set @i = 0
> --All Validation OK, start building the dynamic sql
> statement
>
> Set @sql = ''
> -- User tmp table rows to create the sql statement for
> the crosstab.
> --each row in the table will be a column in the cross-tab
> Set @sql = 'SELECT ISNULL(Convert(varchar(255), ' +
> @crossrow + '),"Undefined") AS ' +
>
> @crossrow + ',' + @CrLF + Space(4)
> --set @sql = 'SELECT' + @crossrow + ',' + char(13)
>
> Declare cross_sql cursor for
> SELECT crosscolumn
> FROM #crosscol
> ORDER BY crosscolumn
>
> --print 'Sql cross statement: ' + @sql
>
> Open cross_sql
> Fetch next from cross_sql into @colname
> --Use "@i" to check for the last column, We need to input
> commas
> --between columns, but not after the last column
> Set @i = 0
> While @@FETCH_STATUS = 0 BEGIN
> Set @i = @i + 1
> Set @colname = ISNULL(@colname,'Undefined')
>
>
> Set @crossvalue = ISNULL(@crossvalue,0)
>
> Set @sql = @sql + '''' +
> convert(varchar(128),@colname) +
> ''' = sum(case convert(varchar(128),' + @crosscolumn
> + ')'
> + char(13) + char(10) + space(8) +
> ' when ''' + @colname + ''' then ' +
> @crossvalue + ' else 0 end) '
>
> IF @i < @cols
> Set @sql = @sql + ',' + @CrLF + Space(4)
> ELSE
> Set @sql = @sql + @CrLF
>
> Fetch next from cross_sql into @colname
> END
>
> close cross_sql
> deallocate cross_sql
>
> Set @sql = @sql + ' from ' + @tablename + ' Group By ' +
> @crossrow
>
> IF LEN( @sql) >= @max_statement_len BEGIN
> Raiserror ('Crosstab sql statement cannot exceed 7999
> characters',@err_severity,1)
> Return 0
> END
>
> Exec ( @sql)
> Select 'Sql' = @sql
> Set nocount off
> Return 1
>
> END
> END
> END
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Masriaty

2004-03-31, 8:30 am

OK guys...
I will try your ways...
I will inform in 3 or 4 days since I'm too busy at office right now...

Thanks

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Masriaty

2004-03-31, 9:30 am

Dear Bonj,

I have tried your sample. When I run the first coding which is dbo.input
from stored procedure, I get input table with data. But when I run the
second coding which is dbo.output from stored procedure, I just get food
name in 1 column and others are null.

Why this is happened? I just paste you sample in store procedure and run
it. Do I done the wrong thing??

I will give my sample in few days...

Mas

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Bonj

2004-03-31, 5:31 pm

You must have pasted it in wrong. You could have collation settings
different, make sure all the code is exactly as I put it, and all in one
procedure - if it's in different databases for example you could have the
collation settings mangling the strings.
Paste the exact procedure you've got. Or try using integers instead of food
names.

"Bonj" <a@b.com> wrote in message
news:%23p4howpFEHA.712@tk2msftngp13.phx.gbl...
> This code demonstrates the general principle of doing a crosstab in SQL:
>
> create table dbo.input
> (food varchar(255), month varchar(255), quantity int)
>
> insert dbo.input(food, month, quantity)
> values('Bread', 'Jan', 23)
> insert dbo.input(food, month, quantity)
> values('Cheese', 'Jan', 45)
> insert dbo.input(food, month, quantity)
> values('Chillies', 'Jan', 84)
>
> insert dbo.input(food, month, quantity)
> values('Bread', 'Feb', 34)
> insert dbo.input(food, month, quantity)
> values('Cheese', 'Feb', 25)
> insert dbo.input(food, month, quantity)
> values('Chillies', 'Feb', 16)
>
> insert dbo.input(food, month, quantity)
> values('Bread', 'Mar', 4)
> insert dbo.input(food, month, quantity)
> values('Cheese', 'Mar', 47)
> insert dbo.input(food, month, quantity)
> values('Chillies', 'Mar', 36)
>
> create table dbo.output(food varchar(255))
> insert dbo.output(food)
> select distinct food from input
> declare @month varchar(255) --month is the column we're swivelling
> declare @sql varchar(255)
> declare repstat cursor
> local
> fast_forward
> read_only
> for select distinct month from dbo.input
> open repstat
> fetch repstat into @month
> while @@fetch_status=0
> begin
> select @sql='alter table dbo.output add ' + @month +
> ' varchar(255)'
> execute(@sql)
> select @sql='update dbo.output set ' + @month +
> '=i.qtot from dbo.output o join ' +
> '(select food, month, sum(quantity) qtot ' +
> 'from dbo.input where month=''' + @month +
> ''' group by food,month) i ' +
> 'on o.food=i.food'
> execute(@sql)
> fetch repstat into @month
> end
> close repstat
> deallocate repstat
> select * from dbo.output
>
> --there you have it. Just run that and you'll see, you can then
extrapolate
> that to your real data.
>
> "Masriaty" <masriaty_76@yahoo.com> wrote in message
> news:%23n8awviFEHA.628@TK2MSFTNGP10.phx.gbl...
>
>

Bonj

2004-03-31, 5:31 pm

anybody else try this? it works fine for me...

"Masriaty" <masriaty_76@yahoo.com> wrote in message
news:ez18uayFEHA.692@TK2MSFTNGP09.phx.gbl...
> Dear Bonj,
>
> I have tried your sample. When I run the first coding which is dbo.input
> from stored procedure, I get input table with data. But when I run the
> second coding which is dbo.output from stored procedure, I just get food
> name in 1 column and others are null.
>
> Why this is happened? I just paste you sample in store procedure and run
> it. Do I done the wrong thing??
>
> I will give my sample in few days...
>
> Mas
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Masriaty

2004-03-31, 10:30 pm

Hi..

There you go... I paste your sample in 2 different procedure.. That's
way is not working... I will try tonight and inform what's the result...

Sorry about that
Masriaty

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Sponsored Links