Dynamic Cross Tab Using Cursors.

[Jaidev Paruchuri]

I have two tables
1)Rollout_detai l
start_date Datetime,
contract_date Datetime,
budget_amt Money
store_id int(foriegn key referring store.store_id)
pan_number varchar(20)
roll_id int

2)store
store_id int(primary key)
skey varchar(10)

these two tables are tied with store_id
and in rollout_detail there can be many pan_numbers for a given
store.(pan_numb er + store ) are unique.

Now here is the problem.

I need to generate a cross tab report with
store,pan1_cont ract_date,pan1_ budget_amt,pan1 _start_date,
pan2_Contract_d ate,pan2_budget _amt,pan2_start _date and so on.

I tried this with in a procedure.
-----------------------------------------------------------
CREATE PROCEDURE crosstab
@roll_id INT
WITH ENCRYPTION
AS
DECLARE @sql VARCHAR(8000),@ panNumber VARCHAR(20)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
DECLARE al_cursor CURSOR FOR SELECT DISTINCT pan_number AS p_pannumber
FROM rollout_detail WHERE roll_id=@roll_i d
OPEN al_cursor
SELECT @sql='SELECT skey, '
FETCH NEXT FROM al_cursor INTO @panNumber
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql=@sql+'(CAS E pan_number WHEN
'''+rtrim(@panN umber)+''' THEN start_date END) as
P'+rtrim(@panNu mber)+'_sd,(CAS E pan_number WHEN
'''+rtrim(@panN umber)+''' THEN budget_amt END) as
P'+rtrim(@panNu mber)+'_ba,(CAS E pan_number WHEN
'''+rtrim(@panN umber)+''' THEN contract_date END) as
P'+rtrim(@panNu mber)+'_cd,'

FETCH NEXT FROM al_cursor INTO @panNumber
END
SELECT @sql=left(@sql, len(@sql)-1)+' '
SELECT @sql=@sql+'FROM rollout_detail rd,store s
WHERE rd.store_id=s.s tore_id AND roll_id='+cast( @roll_id as varchar)

EXEC (@sql)
CLOSE al_cursor
DEALLOCATE al_cursor
SET ANSI_WARNINGS ON

EXECUTE crosstab 1
-----------------------------------------------------------
I am getting multiple records for the same store
the result set is.

skey pan1_cd pan1_ba pan1_sd pan2_cd pan2_ba pan2_sd
1 12/2/04 400.0 3/4/05 NULL NULL NULL
1 NULL NULL NULL 5/6/04 566.00 3/4/04
I want the result set merged for each store.

Please help,

Thanks a million

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

[Simon Hayes]

"Jaidev Paruchuri" <ja****@hotmail .com> wrote in message
news:40******** *************@n ews.frii.net...

I have two tables
1)Rollout_detai l
start_date Datetime,
contract_date Datetime,
budget_amt Money
store_id int(foriegn key referring store.store_id)
pan_number varchar(20)
roll_id int

2)store
store_id int(primary key)
skey varchar(10)

these two tables are tied with store_id
and in rollout_detail there can be many pan_numbers for a given
store.(pan_numb er + store ) are unique.

Now here is the problem.

I need to generate a cross tab report with
store,pan1_cont ract_date,pan1_ budget_amt,pan1 _start_date,
pan2_Contract_d ate,pan2_budget _amt,pan2_start _date and so on.

I tried this with in a procedure.
-----------------------------------------------------------
CREATE PROCEDURE crosstab
@roll_id INT
WITH ENCRYPTION
AS
DECLARE @sql VARCHAR(8000),@ panNumber VARCHAR(20)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
DECLARE al_cursor CURSOR FOR SELECT DISTINCT pan_number AS p_pannumber
FROM rollout_detail WHERE roll_id=@roll_i d
OPEN al_cursor
SELECT @sql='SELECT skey, '
FETCH NEXT FROM al_cursor INTO @panNumber
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql=@sql+'(CAS E pan_number WHEN
'''+rtrim(@panN umber)+''' THEN start_date END) as
P'+rtrim(@panNu mber)+'_sd,(CAS E pan_number WHEN
'''+rtrim(@panN umber)+''' THEN budget_amt END) as
P'+rtrim(@panNu mber)+'_ba,(CAS E pan_number WHEN
'''+rtrim(@panN umber)+''' THEN contract_date END) as
P'+rtrim(@panNu mber)+'_cd,'

FETCH NEXT FROM al_cursor INTO @panNumber
END
SELECT @sql=left(@sql, len(@sql)-1)+' '
SELECT @sql=@sql+'FROM rollout_detail rd,store s
WHERE rd.store_id=s.s tore_id AND roll_id='+cast( @roll_id as varchar)

EXEC (@sql)
CLOSE al_cursor
DEALLOCATE al_cursor
SET ANSI_WARNINGS ON

EXECUTE crosstab 1
-----------------------------------------------------------
I am getting multiple records for the same store
the result set is.

skey pan1_cd pan1_ba pan1_sd pan2_cd pan2_ba pan2_sd
1 12/2/04 400.0 3/4/05 NULL NULL NULL
1 NULL NULL NULL 5/6/04 566.00 3/4/04
I want the result set merged for each store.

Please help,

Thanks a million

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

You're probably looking for something like this - using MAX() and GROUP BY:

select skey,
max(case when ...) as A,
max(case when ...) as B,
....
from
....
where
....
group by skey

Simon

[Jaidev Paruchuri]

Simon,

That worked.
Thankyou very much for the solution.
Though it was simple,I was thinking about alternate solutions.I really
appreaciate you guys.

--Jay

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