dynamic cross tab without aggregation? PLEASE help

Ok..we have a db that the company wants to store roles in (ie,
'auditor', 'accountant', etc..) that are active for a specific date
range ...its a wierd paradigm, dont ask... but we need a specific # of
roles filled for each date...
here's the script
Create table tblRoles(RoleID int primary key identity(1,1), RoleName
nvarchar(255))
create table tblDates(DateID int primary key identity(1,1),
ProjectDate DateTime)
create table tblRolesForDate(RoleForDateID int primary key identity
(1,1), RoleID int, DateID int, NumberNeeded)
what I need to see is a table from that...like this.
1/1 1/2 1/3 1/4 1/5
auditor 0 5 5 2 0
account 5 5 5 0 15
etc...
any ideas on how to make that work? the dates across the top are
from the dates table, the names in the left column are from roles, and
the #'s come from RolesForDate...
Grrr
Replied in .programming
Please do NOT multi-post.
David Portas

dynamic cross tab without aggregation? PLEASE help

Ok..we have a db that the company wants to store roles in (ie,
'auditor', 'accountant', etc..) that are active for a specific date
range ...its a wierd paradigm, dont ask... but we need a specific # of
roles filled for each date...
here's the script
Create table tblRoles(RoleID int primary key identity(1,1), RoleName
nvarchar(255))
create table tblDates(DateID int primary key identity(1,1),
ProjectDate DateTime)
create table tblRolesForDate(RoleForDateID int primary key identity
(1,1), RoleID int, DateID int, NumberNeeded)
what I need to see is a table from that...like this.
1/1 1/2 1/3 1/4 1/5
auditor 0 5 5 2 0
account 5 5 5 0 15
etc...
any ideas on how to make that work? the dates across the top are
from the dates table, the names in the left column are from roles, and
the #'s come from RolesForDate...
GrrrReplied in .programming
Please do NOT multi-post.
David Portas

dynamic cross tab without aggregation? PLEASE help

Ok..we have a db that the company wants to store roles in (ie,
'auditor', 'accountant', etc..) that are active for a specific date
range ...its a wierd paradigm, dont ask... but we need a specific # of
roles filled for each date...
here's the script
Create table tblRoles(RoleID int primary key identity(1,1), RoleName
nvarchar(255))
create table tblDates(DateID int primary key identity(1,1),
ProjectDate DateTime)
create table tblRolesForDate(RoleForDateID int primary key identity
(1,1), RoleID int, DateID int, NumberNeeded)
what I need to see is a table from that...like this.
1/1 1/2 1/3 1/4 1/5
auditor 0 5 5 2 0
account 5 5 5 0 15
etc...
any ideas on how to make that work? the dates across the top are
from the dates table, the names in the left column are from roles, and
the #'s come from RolesForDate...
GrrrReplied in .programming
Please do NOT multi-post.
--
David Portas

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!