PIVOT Data in IQ from Rows to 1 Column

[nuvenai]

given the below table.

Order Prodname

1 Prody

1 ProdA

2 ProdA

2 ProdX

2 ProdH

How to I convert this to one line per order with the prodnames concatenated into an ordered list:

Order Products

1 ProdA, Prody

2 ProdA, ProdH, ProdX

I can do this easily in SQLServer but not sure how to do this in SAP IQ.

[tayeb_hadjou]

In IQ demo database:

create table orders_tab(Order_ID unsigned integer, Prod_ID varchar(100)) ;

insert into orders_tab values 
(1 ,'Prody'),
(1, 'ProdA'),
(2 ,'ProdA'),
(2 , 'ProdX'),
(2 ,'ProdH') ;

commit ;

Query using LIST :

select Order_ID, LIST(Prod_ID order by Prod_ID) as Products_by_Order
from orders_tab
group by Order_ID
order by Order_ID ;

Result :
Order_ID,Products_by_Order
1,'ProdA,Prody'
2,'ProdA,ProdH,ProdX'

See
LIST Function [Aggregate]

Returns a delimited list of values for every row in a group.