Transpose a table with keeping an ID-CodePudding

I have this long table:

col1	col2	col3
Personne1	nom	Aurélie
Personne1	prenom	Dupont
Personne1	age	25
Personne2	nom	Stéphane
Personne2	prenom	Blanc
Personne2	age	45

I want to transpose it to this shape:

col	nom	prenom	age
Personne1	Aurélie	Dupont	25
Personne2	Stéphane	Blanc	45

I tried this code in Oracle SQL developer, but it doesn't work:

select * 
from table as SourceTable 
pivot (max(col3) for col2 in ('nom', 'prenom', 'age'));

PS: the "age" column is string

Could you help please?

Thank you!

CodePudding user response：

(Code you posted, as well as tags suggest that you use Oracle database. Code that follows uses Oracle.)

One option is to use conditional aggregation.

Sample data:

SQL> with test (col1, col2, col3) as
  2    (select 'pers1', 'nom'   , 'Aurelie'  from dual union all
  3     select 'pers1', 'prenom', 'Dupont'   from dual union all
  4     select 'pers1', 'age'   , '25'       from dual union all
  5     --
  6     select 'pers2', 'nom'   , 'Stephane' from dual union all
  7     select 'pers2', 'prenom', 'Blanc'    from dual union all
  8     select 'pers2', 'age'   , '45'       from dual
  9    )

Query:

 10  select col1,
 11    max(case when col2 = 'nom'    then col3 end) nom,
 12    max(case when col2 = 'prenom' then col3 end) prenom,
 13    max(case when col2 = 'age'    then col3 end) age
 14  from test
 15  group by col1;

COL1  NOM      PRENOM   AGE
----- -------- -------- --------
pers1 Aurelie  Dupont   25
pers2 Stephane Blanc    45

SQL>

Or, pivot - as you tried:

 10  select *
 11  from test
 12  pivot (max(col3) for col2 in ('nom', 'prenom', 'age'));

COL1  'nom'    'prenom' 'age'
----- -------- -------- --------
pers1 Aurelie  Dupont   25
pers2 Stephane Blanc    45

SQL>

You commented that you have problems with age being a string; it certainly is a string as COL3 contains names so it can't be a NUMBER datatype column. Though, it doesn't make any problems.

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               CHAR(5)
 COL2                                               VARCHAR2(6)
 COL3                                               VARCHAR2(8)

SQL> select * from test;

COL1  COL2   COL3
----- ------ --------
pers1 nom    Aurelie
pers1 prenom Dupont
pers1 age    25
pers2 nom    Stephane
pers2 prenom Blanc
pers2 age    45

6 rows selected.

SQL> select *
  2  from test
  3  pivot (max(col3) for col2 in ('nom', 'prenom', 'age'));

COL1  'nom'    'prenom' 'age'
----- -------- -------- --------
pers1 Aurelie  Dupont   25
pers2 Stephane Blanc    45

SQL>