InstantForum.NET v4.1.4Teradata Forumshttp://www.teradata.com/teradataforum/info@teradata.comWed, 03 Dec 2008 16:18:09 GMT20http://www.teradata.com/teradataforum/Topic11847-9-1.aspxRecursive 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. Fri, 11 Jul 2008 08:13:10 GMTjeff_ohttp://www.teradata.com/teradataforum/Topic11847-9-1.aspxThanks. Is there a better one query solution you can think of?RThu, 10 Jul 2008 11:12:57 GMTRon Luebkehttp://www.teradata.com/teradataforum/Topic11847-9-1.aspxIt 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)), 1FROM RTST001GROUP BY 1UNION ALLSELECT ST, CTY || ',' || CITY, LVL+1FROM RTST001 INNER JOIN MYRECON ST = STEAND CTY > CITY)SELECT STE, CITYFROM MYRECQUALIFY RANK() OVER(PARTITION BY STE ORDER BY LVL DESC) = 1;Fri, 13 Jun 2008 18:07:41 GMTjoedsilvahttp://www.teradata.com/teradataforum/Topic11847-9-1.aspxHas 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 FLORISSANTMO ST.LOUISMO SPRINGFIELDAL BIRMINGHAMAL MONTGOMERYI want the result to beMO FLORISSANT, ST.LOUIS, SPRINGFIELDAL BIRMINGHAM, MONTGOMERYFri, 13 Jun 2008 11:29:30 GMTRon Luebke