0

Using postgresql, i try to transform/pivot my data in table 1 (wide format) to a new destination table 2 in a long format, without success :

A simplified example of my problem :

Table 1 

id_profil  | no_h |  P1 |   P2 |   P3 |  Pn
01              1     5      7     x1   ...
01              2     7     78     x2   ...
02              1     5      7     x3   ...

Table 2 , result of Table 1 transformation :

id_profil    | no_h |  parametre   |      valeur 
01                1           P1               5
01                1           P2               7 
01                1           P3              x1 
01                2           P1               7    
01                2           P2              78    
01                2           P3              x2    
02                1           P1               5   
02                1           P2               7
02                1           P3              x3

You can find and use the table 1 structure/data in this sqlfiddle.

I see in some stackoverflow posts that it is possible to use INNER JOIN LATERAL to do that. (See Postgres analogue to CROSS APPLY in SQL Server.) But how do I inject the correct column names into the parameter columns?

Update

In the real database, i have more than 150 columns, so if it's possible to not enter each column name manually in the query, it's probably better.

Community
  • 1
  • 1
reyman64
  • 523
  • 4
  • 34
  • 73
  • Possible duplicate of [unpivot and PostgreSQL](https://stackoverflow.com/questions/1128737/unpivot-and-postgresql) – Brian D Apr 09 '19 at 20:25

1 Answers1

1

You have to use a CASE for the valeur column and cast it to a common type (e.g. text), along with a list of parameter names:

with 
  table1(id_profil, no_h, parametre1, parametre2, parametre3) as (
    VALUES (01, 1, 5, 7, 'x1'::text), 
           (01, 2, 7, 78, 'x2'), 
           (02, 1, 5, 7, 'x3')
    ),
  col as (
    unnest('{parametre1,parametre2,parametre3}'::text[])
    )
select t.id_profil, t.no_h, col as parametre,
       case col when 'parametre1' then t.parametre1::text
                when 'parametre2' then t.parametre2::text
                when 'parametre3' then t.parametre3::text
       end as valeur
from col
cross join table1 t
order by 1, 2, 3
Ezequiel Tolnay
  • 4,302
  • 1
  • 19
  • 28
  • Thanks for this first answer and the upvote, i update my question because i don't specify that in my real base i have more than 150 columns, so i cannot enter each of them... – reyman64 Jun 28 '16 at 07:39
  • @reyman64 you could use pg_attribute to create the col table and the CASE part, and dynamically execute the query with RETURN QUERY EXECUTE. – Ezequiel Tolnay Jun 28 '16 at 12:41
  • Hum thx, i'm not specialist, i try to search an example of dynamic CASE using QUERY EXECUTE on the web. – reyman64 Jun 29 '16 at 12:13