Rollup up specific columns in a group

Database
sm1
N/A

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.

5 REPLIES
N/A

Re: Rollup up specific columns in a group

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)?

sm1
N/A

Re: Rollup up specific columns in a group

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

Re: Rollup up specific columns in a group

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
N/A

Re: Rollup up specific columns in a group

Rohan,

I was out sick, so did not see your post earlier.

The solution you provided works. It was quite brilliant.

sm1
N/A

Re: Rollup up specific columns in a group

Thanks!