InstantForum.NET v4.1.4Teradata Forumshttp://www.teradata.com/teradataforum/info@teradata.comSun, 12 Oct 2008 07:58:22 GMT20http://www.teradata.com/teradataforum/Topic11384-5-1.aspxHi folks, I'm newbie to teradata, so please forgive me if I ask about something obvious ;)I have problem with SQL construction, for example, I have table with some data-------------- cut -------------- CREATE SET TABLE example, NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( PROD_ID VARCHAR (60) NOT NULL, INSTALLED_DATE DATE FORMAT 'YYYY-MM-DD' NOT NULL, PARENT_ID VARCHAR (60) NOT NULL);INSERT INTO example VALUES ('01', '2002-11-01', '100');INSERT INTO exampleVALUES ('02', '2002-12-01', '100');INSERT INTO exampleVALUES ('03', '2003-11-01', '100');INSERT INTO exampleVALUES ('04', '2003-12-01', '100');INSERT INTO exampleVALUES ('05', '2004-01-01', '100');INSERT INTO exampleVALUES ('01', '2002-11-01', '200');INSERT INTO exampleVALUES ('02', '2002-12-01', '200');INSERT INTO exampleVALUES ('03', '2003-11-01', '200');-------------- cut -------------- What I'm trying to achieve is horizontal sequence of two values: PROD_ID and INSTALLED_DATE. I wrote a query to do half of the job:-------------- cut -------------- SELECT S1.PARENT_ID, S1.PROD_ID, MAX(S1.INSTALLED_DATE) OVER ( PARTITION BY S1.PARENT_ID ORDER BY S1.INSTALLED_DATE ASC ROWS BETWEEN 0 PRECEDING AND 0 PRECEDING ) AS DATA0, MAX(S1.INSTALLED_DATE) OVER ( PARTITION BY S1.PARENT_ID ORDER BY S1.INSTALLED_DATE ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ) AS DATA1, MAX(S1.INSTALLED_DATE) OVER ( PARTITION BY S1.PARENT_ID ORDER BY S1.INSTALLED_DATE ASC ROWS BETWEEN 2 PRECEDING AND 2 PRECEDING ) AS DATA2, MAX(S1.INSTALLED_DATE) OVER ( PARTITION BY S1.PARENT_ID ORDER BY S1.INSTALLED_DATE ASC ROWS BETWEEN 3 PRECEDING AND 3 PRECEDING ) AS DATA3, MAX(S1.INSTALLED_DATE) OVER ( PARTITION BY S1.PARENT_ID ORDER BY S1.INSTALLED_DATE ASC ROWS BETWEEN 4 PRECEDING AND 4 PRECEDING ) AS DATA4, MAX(S1.INSTALLED_DATE) OVER ( PARTITION BY S1.PARENT_ID ORDER BY S1.INSTALLED_DATE ASC ROWS BETWEEN 5 PRECEDING AND 5 PRECEDING ) AS DATA5, COUNT(S1.PROD_ID) OVER ( PARTITION BY S1.PARENT_ID ) AS TCountFROM example S1QUALIFY RANK() OVER ( PARTITION BY S1.PARENT_ID ORDER BY S1.INSTALLED_DATE ASC ) = TCount -------------- cut -------------- Maybe it is not very elegant, but it works, except that I don't have idea how to add sequence of PROD_ID columns to each DATAX column. The final result would be something like that:PARENT_ID|DATA0|PROD_ID0|DATA1|PROD_ID1|...|DATAN|PROD_IDNI would be grateful for any suggestions. Best regards.Thu, 08 May 2008 06:17:47 GMTschabluk