Transposing the row into a column header and other as column value in Redshift SQL

Source data - table name temp:

I need to convert the column name source output as a new column header and store the column name value output in it as listed below using Redshift SQL.

I have tried using list agg but it is splitting the rows into a single row with a delimiter instead of separate column

SQL query that I tried:

SELECT
    id, 
    LISTAGG(DISTINCT value , '|') WITHIN GROUP (ORDER BY source) 
FROM
    temp 
GROUP BY
    id 

You could use a CASE to transpose the information. Note that this requires that you know the specific columns that are wanted:

-- INIT database
CREATE TABLE temp (
  source text,
  value text,
  id int
);

INSERT INTO temp(source, value, id) VALUES ('A', 'C123', 111);
INSERT INTO temp(source, value, id) VALUES ('B', 'V123', 111);
INSERT INTO temp(source, value, id) VALUES ('A', 'C456', 222);
INSERT INTO temp(source, value, id) VALUES ('B', 'V456', 222);

select
  id,
  max(case when source = 'A' then value end) as a,
  max(case when source = 'B' then value end) as b
from temp
group by id

Output:

id    a      b
111   C123   V123
222   C456   V456