ANSI sql to dynamically convert rows into column data

Question

I have a dynamic input data as below

   Id  val
   --- -----
    10  A
    10  B
    11  A
    11  B
    11  C
    .   .
    .   .

I need to print it following order.

    Id  Val
   ---- ----
    10  A,B
    11  A,B,C

I need to write single query to convert above data without using any inbuilt functions.

My try: I can try this out by populating data in to some temporary data and update records as below.

Step 1: try loading into temp data with unique id column alone and val as null as below.

create table temp as (select id, null as val from table group by id) with data;

Step 2: Update as follows.

update temp t2 
set val=(case when t2.val is null then t1.val else t1.val || ',' t2.val end) from 
(select val from table t1 where t1.val= t2.val) t1

Now, temp table will have above output... But all i need is , is there anyway to bring this output without using Temp table (single query)..

Answer 1

can try this:

select ID, group_concat(distinct value) as value
from table
group by ID

Answer 2

In Oracle, you would use listagg():

select id, listagg(val, ',') within group (order by val)
from table t
group by id
order by id;

There is not ANSI standard mechanism for doing this operation, to the best of my knowledge.