In this cases I usually do something like this:

Select
SUM(case when t1.year_date = '2000' then t1.total_isu else 0 endcase) AS y0_total_isu,
SUM(case when t1.year_date = '1999' then t1.total_isu else 0 endcase) AS y1_total_isu,
SUM(case when t1.year_date = '1998' then t1.total_isu else 0 endcase) AS y2_total_isu




>I'm trying to bring 3 years of data into one record (or row) to make it easier to perform difference (Y0-Y1) and (Y0-Y2) and percent difference ((Y0-Y1)/Y1)*100 calculations.
>
>In the SQL statement below, I'm getting what looks like a Cartessian Join effect. What am I doing wrong?
>
>The PERSISTENCY_STATE table looks something like this:
>geo_code CHAR(3)
>year_date CHAR(4)
>quarter_date CHAR(1)
>lob_code CHAR(1)
>total_isu INT
>
>The GEOGRAPHIC table looks something like this:
>company_code CHAR(1)
>territory_code CHAR(1)
>geo_code CHAR(3)
>name CHAR(20)
>
>SELECT
>geo.company_code,
>geo.territory_code,
>geo.geo_code,
>geo.name,
>y0.year_date,
>y0.quarter_date,
>y0.lob_code,
>SUM(y0.total_isu) AS y0_total_isu,
>SUM(y1.total_isu) AS y1_total_isu,
>SUM(y2.total_isu) AS y2_total_isu
>FROM geographic geo
>LEFT OUTER JOIN persistency_state y0
>ON geo.geo_code = y0.geo_code
>AND y0.year_date = '2000'
>AND y0.quarter_date = '1'
>LEFT OUTER JOIN persistency_state y1
>ON geo.geo_code = y1.geo_code
>AND y1.year_date = '1999'
>AND y1.quarter_date = '1'
>LEFT OUTER JOIN persistency_state y2
>ON geo.geo_code = y2.geo_code
>AND y2.year_date = '2000'
>AND y2.quarter_date = '1'
>GROUP BY geo.company_code,geo.territory_code,geo.geo_code,geo.name,y0.year_date,y0.quarter_date,y0.lob_code
>
>Thanks for your help,
>
>Dan Rhymes