All Forums Database
sm1 4 posts Joined 04/15
03 Apr 2015
Rollup up specific columns in a group

Hi,
I am running into issues while rolling up data from specific columns in a group and diplaying the results on a view. Any help would be appreciated.

SRC TABLE:
ID     SRC     TRGT     AMT
1       sss       aaa        2
1       bbb      ccc         1
1       ddd      jjj           5
1       zzz      mmm      6

 
DESIRED VIEW OUTPUT

ID   SRC  TRGT   AMT  SRC_ROLLED_UP   TRGT_ROLLED_UP    ………DATA FROM OTHER TABLES USING JOIN
1     sss    aaa      2       sss,bbb,ddd,zzz        aaa,ccc,jjj,mmm         .................................
1     bbb   ccc       1       sss,bbb,ddd,zzz        aaa,ccc,jjj,mmm         .................................
1     ddd   jjj         5      sss,bbb,ddd,zzz         aaa,ccc,jjj,mmm         .............................
1     zzz    mmm   6       sss,bbb,ddd,zzz        aaa,ccc,jjj,mmm          .................................

With a recursive view the data can be rolled up, but the fact (or my ignorance) that a recursive view/query can not be inlcuded in a normal view is making it difficult for me.

dnoeth 4628 posts Joined 11/04
06 Apr 2015

 
You need to seperate the rollup select and then join to it, but a recursive view cna't be used in another view (as you already noticed).
Is there a known maximum number of rows per ID?
What's your TD release, are XML Services available (XMLAGG function)?

Dieter

sm1 4 posts Joined 04/15
06 Apr 2015

Hi Dnoeth,
Thank you for your response. Our Teradata Release is 14.10.03.07 and Version is 14.10.03.06. I am not sure about the XMLAGG function. Where and how do I check it?
In my original post, I had missed an important column that drives the rollup. Sorry about that. Below is the new version of the SRC table.
SRC TABLE:
ID     SRC     TRGT     AMT   SEQUENCE_NBR
1       sss       aaa        2        1
1       bbb      ccc         1        2
1       ddd      jjj           5        3
1       zzz      mmm      6        4
The maximum no of rows per id can be 24. Here is how I had planned to code the requirement:
STEP 1: ROLLUP DATA

create recursive view  test (ID, SRC, TRGT, AMT, SEQUENCE_NBR) as
(
select 
      ID,
      SRC (VARCHAR(100)),
      TRGT (VARCHAR(100)),
      AMT, 
      SEQUENCE_NBR 
from SRC              
where sequence_nbr=1

union all

select 
     ID,
     TRIM(TEST.SRC) || ', ' || SEG2.SRC,
     TRIM(TEST.TRGT) || ', ' || SEG2.TRGT,
     AMT,
     SEQUENCE_NBR 
from SRC  inner join test
on
test.id=src.id
and test.sequence_nbr+1=src.sequence_nbr
)

STEP 2 (This is my intended view to be read by COGNOS): Join the view created in STEP 1, to other tables:

SELECT REQUIRED_COLUMNS FROM A, B, (SELECT ID, SRC, TRGT, AMT, SEQUENCE_NBR, RANK() OVER(PARTITION BY id ORDER BY SEG_ID_NBR DESC) AS RNK FROM test QUALIFY RNK=1) C WHERE A.ID=B.ID AND A.ID=C.ID

Rohan_Sawant 55 posts Joined 07/14
07 Apr 2015

Hi sm1,
 
The below sequel will do the required only if the maximum no. rows per id is 24. If its greater than 24 then you need to add "||MAX(CASE WHEN SEQUENCE_NBR = 24 THEN (','||TRIM(TRGT)) ELSE '' END)". 
 

/* Creating test data */
CREATE MULTISET VOLATILE TABLE VT_SRC_TABLE, NO FALLBACK , NO LOG,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
  ID INTEGER,
  SRC VARCHAR(100),
  TRGT VARCHAR(100),
  AMT INTEGER,
  SEQUENCE_NBR INTEGER
)
PRIMARY INDEX (ID,SEQUENCE_NBR)
ON COMMIT PRESERVE ROWS;

INSERT INTO VT_SRC_TABLE VALUES (1,'sss','aaa',2,1);
INSERT INTO VT_SRC_TABLE VALUES (1,'bbb','ccc',1,2);
INSERT INTO VT_SRC_TABLE VALUES (1,'ddd','jjj',5,3);
INSERT INTO VT_SRC_TABLE VALUES (1,'zzz','mmm',6,4);
INSERT INTO VT_SRC_TABLE VALUES (2,'ssss','aaaa',4,1);
INSERT INTO VT_SRC_TABLE VALUES (2,'bbbb','cccc',2,2);
INSERT INTO VT_SRC_TABLE VALUES (2,'dddd','jjjj',10,3);
INSERT INTO VT_SRC_TABLE VALUES (2,'zzzz','mmmm',12,4);
INSERT INTO VT_SRC_TABLE VALUES (2,'llll','qqqq',24,5);
/* Completed creating test data */

/*  Your required sequel */
SELECT
  A.ID
, A.SRC
, A.TRGT
, A.AMT 
, B.SRC_ROLLED_UP
, B.TRGT_ROLLED_UP
FROM
  VT_SRC_TABLE A
INNER JOIN
  (
    SELECT  
      ID
    , MAX(CASE WHEN SEQUENCE_NBR = 1 THEN TRIM(SRC)  ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 2 THEN (','||TRIM(SRC)) ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 3 THEN (','||TRIM(SRC)) ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 4 THEN (','||TRIM(SRC)) ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 5 THEN (','||TRIM(SRC)) ELSE '' END)
      ||MAX(CASE WHEN SEQUENCE_NBR = 6 THEN (','||TRIM(SRC)) ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 7 THEN (','||TRIM(SRC)) ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 8 THEN (','||TRIM(SRC)) ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 9 THEN (','||TRIM(SRC)) ELSE '' END)
      ||MAX(CASE WHEN SEQUENCE_NBR = 10 THEN (','||TRIM(SRC)) ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 11 THEN (','||TRIM(SRC)) ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 12 THEN (','||TRIM(SRC)) ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 13 THEN (','||TRIM(SRC)) ELSE '' END)  
      ||MAX(CASE WHEN SEQUENCE_NBR = 14 THEN (','||TRIM(SRC)) ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 15 THEN (','||TRIM(SRC)) ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 16 THEN (','||TRIM(SRC)) ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 17 THEN (','||TRIM(SRC)) ELSE '' END)
      ||MAX(CASE WHEN SEQUENCE_NBR = 18 THEN (','||TRIM(SRC)) ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 19 THEN (','||TRIM(SRC)) ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 20 THEN (','||TRIM(SRC)) ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 21 THEN (','||TRIM(SRC)) ELSE '' END)
      ||MAX(CASE WHEN SEQUENCE_NBR = 22 THEN (','||TRIM(SRC)) ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 23 THEN (','||TRIM(SRC)) ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 24 THEN (','||TRIM(SRC)) ELSE '' END) 
      AS SRC_ROLLED_UP
    , MAX(CASE WHEN SEQUENCE_NBR = 1 THEN TRIM(TRGT)  ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 2 THEN (','||TRIM(TRGT)) ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 3 THEN (','||TRIM(TRGT)) ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 4 THEN (','||TRIM(TRGT)) ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 5 THEN (','||TRIM(TRGT)) ELSE '' END)
      ||MAX(CASE WHEN SEQUENCE_NBR = 6 THEN (','||TRIM(TRGT)) ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 7 THEN (','||TRIM(TRGT)) ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 8 THEN (','||TRIM(TRGT)) ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 9 THEN (','||TRIM(TRGT)) ELSE '' END)
      ||MAX(CASE WHEN SEQUENCE_NBR = 10 THEN (','||TRIM(TRGT)) ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 11 THEN (','||TRIM(TRGT)) ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 12 THEN (','||TRIM(TRGT)) ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 13 THEN (','||TRIM(TRGT)) ELSE '' END)  
      ||MAX(CASE WHEN SEQUENCE_NBR = 14 THEN (','||TRIM(TRGT)) ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 15 THEN (','||TRIM(TRGT)) ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 16 THEN (','||TRIM(TRGT)) ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 17 THEN (','||TRIM(TRGT)) ELSE '' END)
      ||MAX(CASE WHEN SEQUENCE_NBR = 18 THEN (','||TRIM(TRGT)) ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 19 THEN (','||TRIM(TRGT)) ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 20 THEN (','||TRIM(TRGT)) ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 21 THEN (','||TRIM(TRGT)) ELSE '' END)
      ||MAX(CASE WHEN SEQUENCE_NBR = 22 THEN (','||TRIM(TRGT)) ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 23 THEN (','||TRIM(TRGT)) ELSE '' END) 
      ||MAX(CASE WHEN SEQUENCE_NBR = 24 THEN (','||TRIM(TRGT)) ELSE '' END) 
      AS TRGT_ROLLED_UP   
      FROM 
        VT_SRC_TABLE
      GROUP BY 1
    ) B
ON A.ID = B.ID
ORDER BY A.ID,A.SEQUENCE_NBR;

 
Please let me know in case of issues.
 
Thanks,
Rohan Sawant

sm1 4 posts Joined 04/15
09 Apr 2015

Rohan,
I was out sick, so did not see your post earlier.
The solution you provided works. It was quite brilliant.

sm1 4 posts Joined 04/15
09 Apr 2015

Thanks!

You must sign in to leave a comment.