|
|
|
Forum Newbie
Group: Forum Members
Last Login: 6/10/2008 1:50:21 PM
Posts: 1,
Visits: 30
|
|
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.
|
|
|
|