|
|
|
Forum Member
 
Group: Forum Members
Last Login: 11/18/2008 2:43:07 PM
Posts: 31,
Visits: 41
|
|
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: 11/12/2008 9:19:53 PM
Posts: 469,
Visits: 463
|
|
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: 11/18/2008 2:43:07 PM
Posts: 31,
Visits: 41
|
|
Thanks. Is there a better one query solution you can think of?
R
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 11/25/2008 1:20:39 PM
Posts: 91,
Visits: 119
|
|
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.
|
|
|
|