|
Archives of the TeradataForumMessage Posted: Wed, 14 May 2008 @ 16:47:49 GMT
Hi 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 example VALUES ('02', '2002-12-01', '100'); INSERT INTO example VALUES ('03', '2003-11-01', '100'); INSERT INTO example VALUES ('04', '2003-12-01', '100'); INSERT INTO example VALUES ('05', '2004-01-01', '100'); INSERT INTO example VALUES ('01', '2002-11-01', '200'); INSERT INTO example VALUES ('02', '2002-12-01', '200'); INSERT INTO example VALUES ('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 TCount FROM example S1 QUALIFY 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_IDN I would be grateful for any suggestions. Best regards.
| ||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||
Copyright 2016 - All Rights Reserved | ||||||||||||||||||||||||||||||||||||||||||||||||
Last Modified: 15 Jun 2023 | ||||||||||||||||||||||||||||||||||||||||||||||||