|
|
|
Forum Member
 
Group: Forum Members
Last Login: 8/27/2008 1:28:16 PM
Posts: 28,
Visits: 25
|
|
Has anyone used the WITH RECURSIVE to do concatenation of strings in Teradata. I have used this in other DBs, but every method I have tried is being blocked by one restriction or another (usually forbidding a derived table or restrictions on ROW_NUMBER()).
UDFs are out as the security Nazis have forbidden them. I would REALLY prefer not to use a temp table or stored proc.
For example, If I have a table,
COL1 COL2
---- -----
MO FLORISSANT
MO ST.LOUIS
MO SPRINGFIELD
AL BIRMINGHAM
AL MONTGOMERY
I want the result to be
MO FLORISSANT, ST.LOUIS, SPRINGFIELD
AL BIRMINGHAM, MONTGOMERY
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 8/24/2008 2:47:14 PM
Posts: 425,
Visits: 398
|
|
It kind of gets ugly, and is sort of a cross join in disguise ... I wouldn't sign my name on performance ;) ... but is there for academic purposes..
WITH RECURSIVE MYREC(STE, CITY, LVL)
AS
(
SELECT ST, MIN(CTY) (VARCHAR(1000)), 1
FROM RTST001
GROUP BY 1
UNION ALL
SELECT ST, CTY || ',' || CITY, LVL+1
FROM RTST001 INNER JOIN MYREC
ON ST = STE
AND CTY > CITY
)
SELECT STE, CITY
FROM MYREC
QUALIFY RANK() OVER(PARTITION BY STE ORDER BY LVL DESC) = 1
;
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 8/27/2008 1:28:16 PM
Posts: 28,
Visits: 25
|
|
Thanks. Is there a better one query solution you can think of?
R
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 9/4/2008 4:08:05 PM
Posts: 91,
Visits: 117
|
|
Recursive SQL is your best option and you can search this forum for many examples.
Also, you could use Informatica to handle it, or write a stored procedure with a cursor.
|
|
|
|