CODEWITHSUNDEEP
postgresql
user2958297
user2958297

Reputation: 29

Postgresql vertical to horizontal, one table

I am trying to create a small glossary in postgresql. Until now, I have a table with some "vertical values"

General_id  Sing_id     Synonim     Language
1           1               yes             en-GB
2           1               ja              de-DE
3           1               oui             fr-FR
4           2               no              en-GB
5           2               nein            de-DE
6           2               niet            ru-RU


SELECT 
    General_id,
    Sing_id,
    Synonim,
    Language
FROM table1.base

but I would like to have it like this:

Sing_id   en_GB   de_DE   fr_FR   ru_RU    ...

  1         yes      ja      oui
  2         no       nein           niet    ...

Would you like to give me a hint on how to achieve this?

Upvotes: 0

Views: 4229

Answers (1)

Will Ediger
Will Ediger

Reputation: 893

You're talking about 'pivoting'.

See this post: Pivoting in Postgres

Example from post:

SELECT *
FROM crosstab(
  'SELECT
    a date,
    b.desc AS os,
    (random() * 10000 + 1)::int AS value
     FROM generate_series((now() - ''100 days''::interval)::date, now()::date, ''1 DAY''::interval) a,
          (SELECT unnest(ARRAY[''OSX'', ''Windows'', ''Linux'']) AS DESC) b ORDER BY 1,2
  ','SELECT unnest(ARRAY[''OSX'', ''Windows'', ''Linux''])'
) 
AS ct(date date, OSX int, Windows int, Linux int);

Upvotes: 2

Related Questions