VERTICAL STRING CONCATENATION

Database
Enthusiast

VERTICAL STRING CONCATENATION

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

3 REPLIES
Enthusiast

Re: VERTICAL STRING CONCATENATION

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
;

Enthusiast

Re: VERTICAL STRING CONCATENATION

Thanks. Is there a better one query solution you can think of?

R
Teradata Employee

Re: VERTICAL STRING CONCATENATION

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.