Dynamic Cross Tab Using Cursors.

I have two tables
1)Rollout_detail
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_number + store ) are unique.

Now here is the problem.

I need to generate a cross tab report with
store,pan1_contract_date,pan1_budget_amt,pan1_star t_date,
pan2_Contract_date,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_id
OPEN al_cursor
SELECT @.sql='SELECT skey, '
FETCH NEXT FROM al_cursor INTO @.panNumber
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.sql=@.sql+'(CASE pan_number WHEN
'''+rtrim(@.panNumber)+''' THEN start_date END) as
P'+rtrim(@.panNumber)+'_sd,(CASE pan_number WHEN
'''+rtrim(@.panNumber)+''' THEN budget_amt END) as
P'+rtrim(@.panNumber)+'_ba,(CASE pan_number WHEN
'''+rtrim(@.panNumber)+''' THEN contract_date END) as
P'+rtrim(@.panNumber)+'_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.store_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!"Jaidev Paruchuri" <jaip26@.hotmail.com> wrote in message
news:408fbf3d$0$200$75868355@.news.frii.net...
> I have two tables
> 1)Rollout_detail
> 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_number + store ) are unique.
> Now here is the problem.
> I need to generate a cross tab report with
> store,pan1_contract_date,pan1_budget_amt,pan1_star t_date,
> pan2_Contract_date,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_id
> OPEN al_cursor
> SELECT @.sql='SELECT skey, '
> FETCH NEXT FROM al_cursor INTO @.panNumber
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SELECT @.sql=@.sql+'(CASE pan_number WHEN
> '''+rtrim(@.panNumber)+''' THEN start_date END) as
> P'+rtrim(@.panNumber)+'_sd,(CASE pan_number WHEN
> '''+rtrim(@.panNumber)+''' THEN budget_amt END) as
> P'+rtrim(@.panNumber)+'_ba,(CASE pan_number WHEN
> '''+rtrim(@.panNumber)+''' THEN contract_date END) as
> P'+rtrim(@.panNumber)+'_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.store_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|||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!