sql - Execute query string and get result with varying number of columns

Question

I am trying to transform a list of correlation values into a correlation matrix.
But I can't get the result after executing a string query.

The list/table(named corrtemp) is like this:

| name | colname | value |
| ---- | ------- | ----- |
| a    | a       | 1     |
| a    | b       | 0.5   |
| a    | c       | 0.3   |
| b    | a       | 0.5   |
| b    | b       | 1     |
| b    | c       | 0.2   |
| c    | a       | 0.3   |
| c    | b       | 0.2   |
| c    | c       | 1     |

The output should be like this:

| name |  a  |  b  |  c  |
| ---- |-----| --- | --- |
| a    |  1  | 0.5 | 0.3 |
| b    | 0.5 |  1  | 0.2 |
| c    | 0.3 | 0.2 |  1  |

The number of distinct names is unknown. So I loop to generate a string query. I want to execute it and get the query result in the result console. Currently, my code is like this:

do $$
declare 
    i integer := 0;
     sql0 varchar := 'SELECT DISTINCT temp0.name, cor0.value AS '||(SELECT DISTINCT name FROM corrtemp LIMIT 1)||' FROM corrtemp AS temp0'||chr(13);
     sql1 varchar := 'left JOIN corrtemp AS cor0 ON cor0.colname = temp0.name AND cor0.name = ''a'''||chr(13);
     sql2 varchar := 'WITH temp';
     sql4 varchar;
begin
    sql4 := sql0 || sql1 ||chr(13);
    i := i + 1;
    while i < (SELECT COUNT(DISTINCT name) FROM corrtemp) loop
        sql4 := chr(13)|| 
                sql2|| 
                CAST(i AS VARCHAR(5)) || 
                ' AS('||
                chr(13)||
                sql4||
                ')' ||
                'SELECT temp'|| CAST(i AS VARCHAR(5)) || '.*, cor'|| CAST(i AS VARCHAR(5)) ||'.value AS '||(SELECT DISTINCT name FROM corrtemp LIMIT 1 OFFSET i)||' FROM  temp'|| CAST(i AS VARCHAR(5))||chr(13)||
                'left JOIN corrtemp AS cor'|| CAST(i AS VARCHAR(5)) ||  ' ON cor'|| CAST(i AS VARCHAR(5)) || '.colname = temp'|| CAST(i AS VARCHAR(5)) || '.name AND cor'|| CAST(i AS VARCHAR(5)) || '.name = '''|| (SELECT DISTINCT name FROM corrtemp LIMIT 1 OFFSET i)||''''||chr(13);
        i := i + 1;
        end loop;
end$$

I tried EXECUTE sql4 inside the do and it only returns an 'OK' in the information console.

I need the query result to display in the result console just like after simply running SELECT * FROM corrtemp.

question from:https://stackoverflow.com/questions/65948501/execute-query-string-and-get-result-with-varying-number-of-columns

Answer 1

Unfortunately DO statement hasn't any input/output possibilities. You can change data inside DO statement, but you cannot to use DO statement to get any result.