All Forums Analytics
mphipps 1 post Joined 02/11
10 Feb 2011
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?

emilwu 72 posts Joined 12/07
18 Feb 2011

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

emilwu 72 posts Joined 12/07
18 Feb 2011

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. .

AshishPatil 24 posts Joined 05/12
07 Mar 2013

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

11 Mar 2013

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

AshishPatil 24 posts Joined 05/12
15 Mar 2013

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

AshishPatil 24 posts Joined 05/12
15 Mar 2013

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

AshishPatil 24 posts Joined 05/12
15 Mar 2013

Kindly help me for same

18 Mar 2013

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

Thanx,
Mahesh

KS42982 137 posts Joined 12/12
18 Mar 2013

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.

21 Mar 2013

Hi Rajeev/KS,
           
                 We've achived it by using COALESCE function.
 
WITH RECURSIVE RSLT (DEALER_WID,DIVN_WID, EVTLOC_WID,MONTH_WID,LM_WID,LLM_WID,MM_WID, EVENT_NAME,C0_QTY,LVL)
 

AS(

SEL DEALER_WID,DIVN_WID, EVTLOC_WID,MONTH_WID,LM_WID,LLM_WID,MM_WID,CAST ( EVENT_NAME  AS VARCHAR(1000)),C0_QTY,1 AS LVL

FROM  DEV_ETL_USER.M1

WHERE RN1 = 1

 

UNION ALL

SEL

B.DEALER_WID,

B.DIVN_WID, 

B.EVTLOC_WID,

--B.MONTH_WID,

B.MONTH_WID,

B.LM_WID,

B.LLM_WID,

B.MM_WID,

C2.EVENT_NAME || ',' ||B.EVENT_NAME ,

B.C0_QTY,

B.RN1

FROM  DEV_ETL_USER.M1  B INNER JOIN RSLT C2

ON

C2.DEALER_WID=B.DEALER_WID

AND C2.DIVN_WID=B.DIVN_WID

AND COALESCE(C2.EVTLOC_WID,'')=COALESCE(B.EVTLOC_WID,'')

--AND C2.MONTH_WID=B.MONTH_WID

AND C2.MONTH_WID=B.MONTH_WID

AND COALESCE(C2.MM_WID,'')=COALESCE(B.MM_WID,'')

AND B.RN1 = C2.LVL + 1 

)

SEL DEALER_WID,DIVN_WID, EVTLOC_WID,MONTH_WID,LM_WID,LLM_WID,MM_WID, EVENT_NAME,C0_QTY

FROM RSLT

QUALIFY RANK() OVER (PARTITION BY DEALER_WID,DIVN_WID, EVTLOC_WID,MONTH_WID,MM_WID  ORDER BY LVL DESC ) = 1

 

We should thank to our CS guys (RAJAT MALHOTRA)

 
 

Thanx,
Mahesh

MaheshJessy 26 posts Joined 12/10
04 Feb 2015

I will be using new profiel for posting and replying for queries as my old profile was locked.
Old one was Jessy Mahesh Kothapalli
New one will be MaheshJessy
Thank you!

You must sign in to leave a comment.