Teradata Sql Stack Rows Per User

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

Solution 1:

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

SELECTuser, 
   TRIM(TRAILING','FROM (XMLAGG(TRIM(states)||','/* optionally ORDER BY ...*/) (VARCHAR(10000))))
FROM tab
GROUPBY1

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

Solution 2:

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 (                                     
     SELECTUSER                                                         
       ,STATES                                                                              
       ,ROW_NUMBER() OVER (PARTITIONBYUSERORDERBY STATES) AS rn                                     
     FROM yourtable                                     
    ) WITH DATA PRIMARY INDEX(USER) ONCOMMIT PRESERVE ROWS;                                        

    WITHRECURSIVE rec_test(US,ST, LVL)                                     
       AS                                       
       (                                        
        SELECTUSER,STATES (VARCHAR(10)),1FROM temp                                       
        WHERE rn =1UNIONALLSELECTUSER, TRIM(STATES) ||', '|| ST,LVL+1FROM temp INNERJOIN rec_test                                       
        ONUSER= US                                        
       AND temp.rn = rec_test.lvl+1                                     
       )                                        
       SELECT US,ST, LVL                                        
       FROM rec_test                                        
       QUALIFY RANK() OVER(PARTITIONBY US ORDERBY LVL DESC) =1;                                      

Solution 3:

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 (
SELECTuser
    , states
    , ROW_NUMBER() OVER (PARTITIONBYuserORDERBY states) AS rn
    , COUNT(*) OVER (PARTITIONBYuser) AS cnt
FROM yourtable
) WITH DATA 
UNIQUEPRIMARY INDEX(user, rn)
ONCOMMIT PRESERVE ROWS;

WITHRECURSIVE cte (user, list, rn) AS (
SELECTuser
    , CAST(states ASVARCHAR(1000)) -- maximum size based on maximum number of rows * length of states
    , rn
FROM vt
WHERE rn = cnt -- start with last states rowUNIONALLSELECT
    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
)
SELECTuser, 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.