每个用户的 Teradata SQL 堆栈行 [英] Teradata SQL stack rows per user

问题描述

有没有办法按用户堆叠/分组字符串/文本?

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

我有数据

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

我需要的是

    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!

推荐答案

我不是专家，但这应该有效.您可能需要根据您的确切要求对其进行一些修改.希望这会有所帮助！

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 堆栈行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！