Teradata SQL stack rows per user

Question

Is there a way to stack/group string/text per user ?

data I have

    USER    STATES
     1      CA
     1      AR
     1      IN
     2      CA
     3      CA
     3      NY
     4      CA
     4      AL
     4      SD
     4      TX

What I need is

    USER    STATES
     1      CA / AR / IN
     2      CA
     3      CA / NY
     4      CA / AL / SD / TX

I tried cross join and then another cross join however but the data spools out. Thanks!

data is huge. Don't know how to find that eyeballing. I read I could create a recursive table. Looking into that — userNid, Mar 07 '16 at 21:35
why not pivot the states? There's only ... 57 states... right? — access_granted, Mar 07 '16 at 21:55

score 2 · Answer 1 · answered Mar 07 '16 at 22:00

2

If Teradata's XML-services are installed there's a function named XMLAGG, which returns a similar result: CA, AR, IN

SELECT user, 
   TRIM(TRAILING ',' FROM (XMLAGG(TRIM(states)|| ',' /* optionally ORDER BY ...*/) (VARCHAR(10000))))
FROM tab
GROUP BY 1

Btw, using recursion will result in huge spool usage, because you keep all the intermediate rows in spool before returning the final row.

answered Mar 07 '16 at 22:00

dnoeth

59,503
4
39
56

thanks! However we have very limited services available to non-developers. – userNid Mar 07 '16 at 22:25

Kamil Gosciminski · Answer 2 · 2016-03-07T22:07:19.483

Unfortunately there is no GROUP_CONCAT or any string aggregate functions in Teradata (at least none that I'm aware of) so one way to achieve your result would be to use recursion, since you don't know the maximum values of states per user.

For recursion you should use a Volatile Table, as OLAP functions are not allowed in the recursive part. This is a non-tested code (I've got no way of testing it unfortunately), so there might be several bugs, but should give you the concept and with some troubleshooting (if needed) give you expected result.

Replace yourtable in definition of Volatile Table with your real table name.

CREATE VOLATILE TABLE vt AS (
SELECT
    user
    , states
    , ROW_NUMBER() OVER (PARTITION BY user ORDER BY states) AS rn
    , COUNT(*) OVER (PARTITION BY user) AS cnt
FROM yourtable
) WITH DATA 
UNIQUE PRIMARY INDEX(user, rn)
ON COMMIT PRESERVE ROWS;

WITH RECURSIVE cte (user, list, rn) AS (
SELECT
    user
    , CAST(states AS VARCHAR(1000)) -- maximum size based on maximum number of rows * length of states
    , rn
FROM vt
WHERE rn = cnt -- start with last states row

UNION ALL

SELECT
    vt.user
    , cte.list || ',' || vt.states
    , vt.rn
FROM vt
JOIN cte ON vt.user = cte.user AND vt.rn = cte.rn - 1 -- append a row that is rn-1 of your rows for a given user
)
SELECT user, list
FROM cte
WHERE rn = 1; -- going from last to first, in this condition there should be entire list

This solution isn't perfect - it forces the engine to store immediate results in a temporary area during query processing. You may encounter a No more spool space error.

thanks ! unfortunately for the data i have,session hangs when I run the above query. I have a working solution for what I need and will just go with that for now. Highly appreciate your assistance. — userNid, Mar 07 '16 at 22:41
You may post your working solution as an answer for the future readers :-) — Kamil Gosciminski, Mar 07 '16 at 22:42

score 1 · Accepted Answer · answered Mar 07 '16 at 22:02

I am not an expert but this should work. You may need to modify it a bit per your exact requirement. Hope this helps!

    CREATE VOLATILE TABLE temp AS (                                     
     SELECT                                     
       USER                                                         
       ,STATES                                                                              
       ,ROW_NUMBER() OVER (PARTITION BY USER ORDER BY STATES) AS rn                                     
     FROM yourtable                                     
    ) WITH DATA PRIMARY INDEX(USER) ON COMMIT PRESERVE ROWS;                                        

    WITH RECURSIVE rec_test(US,ST, LVL)                                     
       AS                                       
       (                                        
        SELECT USER,STATES (VARCHAR(10)),1                                      
        FROM temp                                       
        WHERE rn = 1                                        
        UNION ALL                                       
        SELECT  USER, TRIM(STATES) || ', ' || ST,LVL+1                                      
        FROM temp INNER JOIN rec_test                                       
        ON USER = US                                        
       AND temp.rn = rec_test.lvl+1                                     
       )                                        
       SELECT US,ST, LVL                                        
       FROM rec_test                                        
       QUALIFY RANK() OVER(PARTITION BY US ORDER BY LVL DESC) = 1;

Teradata SQL stack rows per user

3 Answers3

Linked