row concatenation for dummies? AKA oracle wm_concat() teradata equivalent

Analytics

row concatenation for dummies? AKA oracle wm_concat() teradata equivalent

I've been looking for a solution to this, and I found this: http://forums.teradata.com/forum/enterprise/concatenating-different-values-in-a-field but I'm just not able to figure out how to make it work. I'm a complete noob at teradata, and I've never used a recursive query before.

I've seen the examples where you do a cast() against a known number of child values, but that isn't possible- I don't know how many child values are out there.

Here's what I'm essentially trying to do:

For the sake of the discussion, I have a table with two columns: 'plan' and 'consolidate'. Each value in 'plan' is unique, and no values in 'consolidate' are believed unique. The 'consolidate' field is used to roll up the 'plan' values into operational groups.

The task is to aggregate the values in the rows of 'plan' into a single row for 'consolidate' seperated by commas or some other delimiter.

So for the following values:

|plan |consolidate|
|MO |1 |
|IL |1 |
|AR |1 |
|KS |2 |
|MS |3 |
|LA |3 |
|TX |3 |
|OK |2 |

I'd like to get the following results (in no particular order):

|consolidate |rollup |
|1 |MO, IL, AR |
|2 |KS, OK |
|3 |MS, LA, TX |

Can anyone help with this? Perhaps a recursive isn't the best solution in Teradata?
11 REPLIES
N/A

Re: row concatenation for dummies? AKA oracle wm_concat() teradata equivalent

CREATE RECURSVIE VIEW REC_VIEW AS (CONSOLIDATE, ROLLUP, PLAN) AS(
SELECT CONSOLIDATE, MIN(PLAN) , MIN(PLAN)
FROM MYTABLE
UNION ALL
SELECT B.CONSOLIDATE, B.ROLLUP||','||A.PLAN, A.PLAN
FROM MYTABLE A INNER JOIN REC_VIEW B
ON A.COSOLIDATE = B.CONSOLIDATE AND
A.PLAN > B.PLAN
;

SELECT CONSOLIDATE, ROLLUP
FROM REC_VIEW
ROW_NUMBER() OVER (PARTITION BY CONSOLIDATE ORDER BY CHARACTER_LENGTH(ROLLUP) DESC) = 1
N/A

Re: row concatenation for dummies? AKA oracle wm_concat() teradata equivalent

give it a try, if it blows out... LOL... at your own risk.
typically this type of operation is not relational and I would not use SQL to do it. .

Re: row concatenation for dummies? AKA oracle wm_concat() teradata equivalent

Hi,

Can anyone guide on how can we implement WM_CONCAT as explained above in Teradata ?

In our case some Oracle logics are built on this, there should be some way to replicate this in Teradata kindly help.

Regards,

Ashish

Re: row concatenation for dummies? AKA oracle wm_concat() teradata equivalent

Hi Ashish,

I think its explained above by emilwu, one can use recursive queries to achieve the concatenation

heres another example-

Assuming that tb1 is the table conataining your data

create multiset volatile table tb1

(id integer

,nm varchar(5)

)   primary index (id,nm)  on commit preserve rows;

insert into tb1 values (10,'xy');

insert into tb1 values (10,'yz');

insert into tb1 values (10,'zx');

insert into tb1 values (20,'ab');

insert into tb1 values (20,'bc');

create multiset volatile table tb3

as

(

sel

id

,nm

,rank () over (partition by id order by nm) rn1

from

tb1

) with data primary index (id,nm) on commit preserve rows;

with recursive rslt (id,nm,rn1,lvl)

as(

sel

id,

cast ( nm  as varchar(20)),

rn1,

0 as lvl

from

tb3

where

rn1 = 1

union all

sel

rslt.id,

rslt.nm || ',' ||b.nm,

b.rn1,

rslt.lvl +1 as lvl

from

tb3  b

inner join

rslt

on

rslt.id = b.id

where

rslt.rn1 < b.rn1

)

 sel id,nm

 from rslt

 qualify rank() over (partition by id  order by lvl desc ) = 1;

Let me know if this helps

Regards

R.Rajeev

Re: row concatenation for dummies? AKA oracle wm_concat() teradata equivalent

Hi Rajeev,

Thanks for giving solution. But the problem is this logic is working if i have only one column in GROUP BY and ORDER BY.

This is not working if i have multiple columns for grouping.

EX:
My Input data is

DEALER_WID DIVN_WID EVTLOC_WID MONTH_WID MM_WID EVENT_NAME
16 85,930 ? 201,208 ? INDEPENDENCE_PCBU_EVENT
16 85,930 ? 201,208 ? 15AUGUST_EVENT

Output should be,

DEALER_WID DIVN_WID EVTLOC_WID MONTH_WID MM_WID EVENT_NAME
16 85930 NULL 201208 NULL INDEPENDENCE_PCBU_EVENT,15AUGUST_EVENT <<----- Concatenated

Kindly help to achieve this.

Regards,
Ashish

Re: row concatenation for dummies? AKA oracle wm_concat() teradata equivalent

DEALER_WID DIVN_WID                       EVTLOC_WID           MONTH_WID                MM_WID             EVENT_NAME

16                     85,930                              ?                        201,208                           ?                INDEPENDENCE_PCBU_EVENT

16                     85,930                              ?                         201,208                           ?                15AUGUST_EVENT

Output should be, (Required output)

DEALER_WID  DIVN_WID                    EVTLOC_WID                          MONTH_WID MM_WID                                           EVENT_NAME

16                      85930                      NULL                                            201208 NULL                       INDEPENDENCE_PCBU_EVENT,15AUGUST_EVENT

Re: row concatenation for dummies? AKA oracle wm_concat() teradata equivalent

Kindly help me for same

Re: row concatenation for dummies? AKA oracle wm_concat() teradata equivalent

Hi Team,

Please suggest me how to hanle multiple grouping statements in recursive function. Please let me know as soon as possible

we (Me and ashish Patil ) are eagerly awaiting for your valuable replay

Re: row concatenation for dummies? AKA oracle wm_concat() teradata equivalent

You need to RANK on all these columns on which you need to do grouping. Keep adding them into order by clause and you will get rank based off of the unique combination of all the columns that you need in grouping. Hope it helps.