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
Highlighted
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
2 REPLIES
Highlighted
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
Enthusiast

Re: Recursive Output

Thanks Welder its working fine