I have a table which has data like :-
id name data
1 a 10
1 b 20
1 c 30
2 d 40
2 e 50
Now, If I want to convert the rows into columns(giving them a unique name) and aggregate the data row (using xmlagg) ... How can I get output like
id name_1 name_2 name_3 data
1 a b c 10,20,30
2 d e 40,50
So basically convert rows into columns and rename them and aggregate the data related to them.
As an example: you can use listAgg or WM_Concat depending on version of Oracle
SELECt ID, wm_concat(data)
FROM Table
GROUP BY ID
you can also use a case statement but without knowing how you want to determine which names go in what of the 3 columns, I'm not hazarding a guess for it yet...
For this type of query, you need to generate a sequential number and then do conditional aggregation:
select id,
max(case when seqnum = 1 then name end) as name1,
max(case when seqnum = 2 then name end) as name2,
max(case when seqnum = 3 then name end) as name3,
list_agg(data, ',') within group (order by name) as data
from (select t.*, row_number() over (partition by id order by name) as seqnum
from table t
) t
group by id;
