Hi everybody,
I'm writing a stored proc in Sybase for a crosstab report in Crystal 10 and I don't know exactly how to get what I want. The report contains a count of Nationalities by Canadian Region.
I can retrieve all the Nationalities along with their associated region, but I can't convert that to a crosstab format that has counts for each region:
Nationality Port of Arrival
Altantic Prairie Greater Toronto Area ...
Albania 3 7 16
Ghana 1 4 0
Morrocco 25 2 10
Zimbabwe 1 31 7
Here is what I have so far. It results in the following output:
citizenship region_cd region region_1 region_2 region_3 region_4 region_5 region_6
Abu Dhabi 10008261 British Columbia/yukon Region 0 0 1 0 0 0
Armenia 10008260 Atlantic Region 0 1 0 0 0 0
Austria 10008260 Atlantic Region 0 1 0 0 0 0
Azerbaijan 10008260 Atlantic Region 0 1 0 0 0 0
Azerbaijan 10008260 Atlantic Region 0 1 0 0 0 0
Azerbaijan 10008260 Atlantic Region 0 1 0 0 0 0
Azerbaijan 10008264 Quebec Region 0 0 0 0 0 1
Azerbaijan 10008264 Quebec Region 0 0 0 0 0 1
Bahamas Islands 10008260 Atlantic Region 0 1 0 0 0 0
Bahamas Islands 10008260 Atlantic Region 0 1 0 0 0 0
Bahamas Islands 10008260 Atlantic Region 0 1 0 0 0 0
Bahamas Islands 10008260 Atlantic Region 0 1 0 0 0 0
Bahamas Islands 10008260 Atlantic Region 0 1 0 0 0 0
Bahamas Islands 10008260 Atlantic Region 0 1 0 0 0 0
Bahamas Islands 10008260 Atlantic Region 0 1 0 0 0 0
Bahrain 10008260 Atlantic Region 0 1 0 0 0 0
Bahrain 10008260 Atlantic Region 0 1 0 0 0 0
Bahrain 10008260 Atlantic Region 0 1 0 0 0 0
Bahrain 10008260 Atlantic Region 0 1 0 0 0 0
Bahrain 10008260 Atlantic Region 0 1 0 0 0 0
Here is my query so far:
create table #region_count
(
citizenship varchar(50) NOT NULL
,region_cd int NOT NULL
,region varchar(50) NOT NULL
,region_1 int NULL
,region_2 int NULL
,region_3 int NULL
,region_4 int NULL
,region_5 int NULL
,region_6 int NULL
)
insert #region_count
select "citizenship" = isnull (( select case when @language_cd = 'fr'
then french_desc
else english_desc
end
from vcode_value cv
where cv.code_value_id = cit.citizenship_country_cd
), '')
,dr.p_code_value_id
,"region" = isnull ( case when @language_cd = 'fr'
then dr.p_french_desc
else dr.p_english_desc
end, '')
,"region_1" = (case dr.p_code_value_id when 10006446 then 1 else 0 end)
,"region_2" = (case dr.p_code_value_id when 10008260 then 1 else 0 end)
,"region_3" = (case dr.p_code_value_id when 10008261 then 1 else 0 end)
,"region_4" = (case dr.p_code_value_id when 10008262 then 1 else 0 end)
,"region_5" = (case dr.p_code_value_id when 10008263 then 1 else 0 end)
,"region_6" = (case dr.p_code_value_id when 10008264 then 1 else 0 end)
from vssi s
,vclient cl
...
where ...
order by ...
/*************************
**********
**********
**********
**********
**********
*/
/* RESULTS */
/*************************
**********
**********
**********
**********
**********
*/
set nocount off
select *
from #region_count
order by citizenship
,region
Thanks a lot,
Rob
Start Free Trial
