Recursive Output

Database
Enthusiast

Recursive Output

Hi All,

 

Can you pleaese help me in getting the query with respect to  the below result set.

 

Table has the data as like below

 

Customer_idfirst_namelast_nameProduct
1001sachinkumarMonday
1001sachinkumarTuesday
1001sachinkumarWednesday
1002RaviRajMonday
1002RaviRajTuesday
1002RaviRajWednesday

 

 

O/P   
    
Customer_idfirst_namelast_nameProduct
1001sachinkumarMonday,Tuesday,Wednesday
1002RaviRajMonday,Tuesday,Wednesday

 

Thanks in advance

Tags (1)

Accepted Solutions
Teradata Employee

Re: Recursive Output

Hi vinoth_84,

 

You can use XMLAGG aggreate function for this.

Please check in the documentation for the ORDER BY support of the function.

create multiset volatile table mvt_data, no log
( Customer_Id   integer
, First_Name    varchar(10)
, Last_Name     varchar(10)
, Product       varchar(10)
)
primary index (Customer_Id)
on commit preserve rows;

insert into mvt_data values (1001, 'Sachin', 'Kumar', 'Monday'   );
insert into mvt_data values (1001, 'Sachin', 'Kumar', 'Tuesday'  );
insert into mvt_data values (1001, 'Sachin', 'Kumar', 'Wednesday');
insert into mvt_data values (1002, 'Ravi'  , 'Raj'  , 'Monday'   );
insert into mvt_data values (1002, 'Ravi'  , 'Raj'  , 'Tuesday'  );
insert into mvt_data values (1002, 'Ravi'  , 'Raj'  , 'Wednesday');


  select Customer_Id
       , max(First_Name) as First_Name
       , max(Last_Name)  as Last_Name
       , trim(trailing ';' from (XMLAgg(Product || ';') (varchar(100)))) as Products
    from mvt_data
group by Customer_Id;

Customer_Id  First_Name  Last_Name  Products
-----------  ----------  ---------  --------------------------
1001         Sachin      Kumar      Monday; Tuesday; Wednesday
1002         Ravi        Raj        Monday; Tuesday; Wednesday
1 ACCEPTED SOLUTION
4 REPLIES 4
Teradata Employee

Re: Recursive Output

Hi vinoth_84,

 

You can use XMLAGG aggreate function for this.

Please check in the documentation for the ORDER BY support of the function.

create multiset volatile table mvt_data, no log
( Customer_Id   integer
, First_Name    varchar(10)
, Last_Name     varchar(10)
, Product       varchar(10)
)
primary index (Customer_Id)
on commit preserve rows;

insert into mvt_data values (1001, 'Sachin', 'Kumar', 'Monday'   );
insert into mvt_data values (1001, 'Sachin', 'Kumar', 'Tuesday'  );
insert into mvt_data values (1001, 'Sachin', 'Kumar', 'Wednesday');
insert into mvt_data values (1002, 'Ravi'  , 'Raj'  , 'Monday'   );
insert into mvt_data values (1002, 'Ravi'  , 'Raj'  , 'Tuesday'  );
insert into mvt_data values (1002, 'Ravi'  , 'Raj'  , 'Wednesday');


  select Customer_Id
       , max(First_Name) as First_Name
       , max(Last_Name)  as Last_Name
       , trim(trailing ';' from (XMLAgg(Product || ';') (varchar(100)))) as Products
    from mvt_data
group by Customer_Id;

Customer_Id  First_Name  Last_Name  Products
-----------  ----------  ---------  --------------------------
1001         Sachin      Kumar      Monday; Tuesday; Wednesday
1002         Ravi        Raj        Monday; Tuesday; Wednesday
Highlighted
Enthusiast

Re: Recursive Output

Thanks Welder its working fine

Enthusiast

Re: Recursive Output

Hi Welder,

  

Thanks for the Output, 

 

In addition to this if we have duplicate product we must show distinct value not with the dupicate value recursively

 

I tried to insert duplicate record and execute the  above select query with distinct also got the same result as below

 

Customer_IdFirst_NameLast_NameProducts
1,001SachinKumarMonday; Tuesday; Wednesday; Monday
1,002RaviRajMonday; Tuesday; Wednesday

 

Thanks

Teradata Employee

Re: Recursive Output

Hi vinoth_84,

 

Just distinct your data before using XMLAgg :

with cte_data_dst (Customer_Id, First_Name, Last_Name, Product) as
(
  select Customer_Id, First_Name, Last_Name, Product
    from mvt_data
group by Customer_Id, First_Name, Last_Name, Product
)
  select Customer_Id
       , max(First_Name) as First_Name
       , max(Last_Name)  as Last_Name
       , trim(trailing ';' from (XMLAgg(Product || ';') (varchar(100)))) as Products
    from cte_data_dst
group by Customer_Id;