How to select values side-by-side

Posted on

Question :

I have a table like this

id_project    id_field    value
1             1           abc
1             2           xfe
2             3           ufj
2             4           osj

And I have to export the data in a CSV-like manner

field_1    field_2
abc        xfe

I tried this

SELECT 

(SELECT  value
 FROM    table
 WHERE   id_field = 1) AS field_1,

(SELECT  value
 FROM    table
 WHERE   id_field = 2) AS field_2

FROM table
WHERE id_project = 1;

but it does not work.

EDIT: thanks for all the answers, I think I figured out after reading this comment https://stackoverflow.com/a/13065203/1822157

Answer :

For a well-known number of columns you can take advantage of tablefunc module: (Note I’ve added a 3rd column according to your comments)

select *
from   crosstab('select id_project, id_field, v from t order by 1, 2')
       as result(project int, field1 text, field2 text, field3 text);
project | field1 | field2 | field3
------: | :----- | :----- | :-----
      1 | abc    | xfe    | vvv   
      2 | ufj    | osj    | fsd   

db<>fiddle here

You don’t mention what it is that does not work, but clearly:

 WHERE id_project = 1

matches 2 rows, so if the problem is duplicates, that is the root cause. Easiest to solve by adding distinct

SELECT DISTINCT 
(SELECT  value
 FROM    table
 WHERE   id_field = 1) AS field_1,
(SELECT  value
 FROM    table
 WHERE   id_field = 2) AS field_2
FROM table
WHERE id_project = 1;

Note that if id_field is not unique, you need a correlation against the outer select:

SELECT DISTINCT 
(SELECT  value
 FROM    table
 WHERE   id_field = 1
   AND   id_project = t1.id_project ) AS field_1,
(SELECT  value
 FROM    table
 WHERE   id_field = 2
   AND   id_project = t1.id_project ) AS field_2
FROM table as t1
WHERE id_project = 1;

Another option would be:

with t (id_project, id_field, value) as ( 
    values (1,1,'abc')
         , (1,2,'xfe')
         , (2,3,'ufj')
         , (2,4,'osj')
)
SELECT MAX(x.val1), MAX(x.val2)
FROM t
CROSS JOIN LATERAL (
    VALUES (CASE t.id_field WHEN 1 THEN value END
           ,CASE t.id_field WHEN 2 THEN value END)
) x (val1, val2)
WHERE t.id_project = 1;

Leave a Reply

Your email address will not be published. Required fields are marked *