How about this Jim if you haven't already done it. CREATE TEMPORARY TABLE JIM_DATA (COMP_ID INTEGER,FK_ID INTEGER, + COMPYEAR INTEGER,COMPLIANCE INTEGER) -- LOADED JIM_DATA WITH SAMPLE DATA -- DROP VIEW TEMPVIEW -- CREATE TEMPORARY VIEW TEMPVIEW (FK_ID,CUR_YEAR,CUR_YEAR_1,CUR_YEAR_2) + AS SELECT T1.FK_ID,T1.COMPLIANCE,T2.COMPLIANCE,T3.COMPLIANCE + FROM JIM_DATA T1,JIM_DATA T2,JIM_DATA T3 + WHERE T1.FK_ID = T2.FK_ID AND T1.FK_ID = T3.FK_ID AND T1.COMPYEAR = 2005 + AND T2.COMPYEAR = 2004 AND T3.COMPYEAR = 2003 -- RETURN
Hope this helps. Mike Ramsour -----Original Message----- From: James Hageman [mailto:[EMAIL PROTECTED] Sent: Monday, July 25, 2005 10:51 AM To: [email protected] Subject: [RBG7-L] - SQL help - flattening a dataset I have a dataset as follows: ____________________________________ comp_id fk_id year compliance 1 185 2003 97 2 185 2004 100 3 185 2005 100 4 186 2003 96 5 186 2004 97 6 186 2005 98 7 187 2003 56 8 187 2004 78 9 187 2005 98 ------------------------------------- I want to get it into the following format temporarily to show in a report: ______________________________________________________________ fk_id current_year(2005) current-1(2004) current-2(2003) 185 100 100 97 186 98 97 96 187 98 78 56 ----------------------------------------------------------------- Can I do this in a VIEW with a SELF-JOIN or oth SQL command or do I need to run it through some code in a WHILE loop? james
