Archives of the TeradataForum

Message Posted: Wed, 19 Oct 2005 @ 11:07:05 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I don't know if I saw my solution back in the archives. I came up with this generalized solution a while back when I started learning about recursive SQL. This is an example using DB2 syntax, which will be only subtly different than Teradata syntax...

     -- This SQL takes a string field and essentially concatenates the contents from
     -- multiple records together to form a single string for the specified group by.
     --
     -- For example, take these rows:
     -- DEPARTMENT_NUM   EMPLOYEE_LOGON
     -- ---------------- ---------------------
     -- 1                pboal
     -- 1                gphillip
     -- 1                fpetty
     -- 2                lmayer
     -- 2                gmargi
     -- 3                jvanden
     --
     -- And produce results that look like this:
     -- DEPARTMENT_NUM   LOGON_LIST
     -- ---------------- ----------------------
     -- 1                pboal, gphillip, fpetty
     -- 2                lmayer, gmargi
     -- 3                jvanden
     --
     --


     -- -----------------------------------------------------------------------
     -- CREATE THE TABLE AND DATA
     -- -----------------------------------------------------------------------

     create table EMP_LIST (DEPT_NUM integer not null, LOGON_ID varchar(18) not null);
     insert into EMP_LIST values (1, 'pboal');
     insert into EMP_LIST values (1, 'gphillip');
     insert into EMP_LIST values (1, 'fpetty');
     insert into EMP_LIST values (2, 'lmayer');
     insert into EMP_LIST values (2, 'gmargi');
     insert into EMP_LIST values (3, 'jvanden');

     -- -----------------------------------------------------------------------
     -- CREATE VIEW TO ATTACH A SEQ_NUM ONTO EACH ROW (SEQ_NUM IS BY DEPT_NUM)
     -- -----------------------------------------------------------------------
     create view V_EMP_LIST as (
       select DEPT_NUM, LOGON_ID,
              row_number() over (partition by DEPT_NUM order by LOGON_ID) SEQ_NUM
        from  EMP_LIST
     );

     -- -----------------------------------------------------------------------
     -- AND THEN USE RECURSIVE SQL TO FLATTEN OUT THE LISTS
     -- -----------------------------------------------------------------------
     with recursive TEMPTAB (dept_num, logon_ct, logon_list) as
     (
       -- Recursive SQL works best if you start with a "root node", that's why we
       -- added the sequence number by department to the table.
       -- So, in this case, we'll make our root node the first row for each department.
       select root.DEPT_NUM, 1, varchar(root.LOGON_ID,100)
       from   V_EMP_LIST root
       where  root.SEQ_NUM = 1

       union all

       -- The recursive part happens here.
       -- This step will take row#2 (if it exists), joins to row#1 and concatenates
       -- the LOGON_ID values together.
       -- Then it takes row#3 (if it exists), joins to the row you just created and concatenates
       -- the LOGON_ID values to the LOGON_LIST above.
       -- Then it takes row#4... and so forth...
       select sub.DEPT_NUM, super.LOGON_CT + 1, super.LOGON_LIST || ', ' || sub.LOGON_ID
       from   V_EMP_LIST sub, TEMPTAB super
       where  sub.DEPT_NUM = super.DEPT_NUM and sub.SEQ_NUM = super.LOGON_CT + 1 and sub.SEQ_NUM > 1
     )
     -- The output will be each and every row from the process.
     -- DEPT_NUM   LOGON_CT LOGON_LIST
     -- ---------- -------- -------------------------------------
     -- 1          1        fpetty
     -- 1          2        fpetty, gphillip
     -- 1          3        fpetty, gphillip, pboal
     -- 2          1        gmargi
     -- 2          2        gmargi, lmayer
     -- 3          1        jvanden

     -- Then this part of the SQL just pulls results from the recursive temp table defined
     -- in the WITH clause.  This does a subselect so that we can just pick the record that has
     -- the largest number of logons within the list, by department.
     select DEPT_NUM, LOGON_LIST from (
       select DEPT_NUM, rank() over (partition by DEPT_NUM order by LOGON_CT desc) RNK, LOGON_LIST from TEMPTAB
     ) z
     where RNK = 1
     ;

     drop view  V_EMP_LIST;
     drop table EMP_LIST;

	<Prev	Next>		<<First	<Prev	Next>	Last>>