Postrge: rows to columns

Question

i have table which contains street names in several languages:

streetName(addressId uuid, languageCode text, name text);

with values:

addressid                            |languagecode |name            |
-------------------------------------|-------------|----------------|
e5c8c25c-f21e-47df-9172-7f3c7e52d669 |cz           |streetName_1_cz |
e5c8c25c-f21e-47df-9172-7f3c7e52d669 |en           |streetName_1_en |
e5c8c25c-f21e-47df-9172-7f3c7e52d669 |fi           |streetName_1_fi |
e5c8c25c-f21e-47df-9172-7f3c7e52d669 |sv           |streetName_1_sv |
bff096cc-4d4d-4b2e-aac2-bdc6ab659a72 |fi           |streetName_2_fi |
bff096cc-4d4d-4b2e-aac2-bdc6ab659a72 |cz           |streetName_2_cz |

and need to transform street names in cz, fi, en to columns. (exactly those three languages even if there are more languages in the table, and it can happen, that value for some of those three language is missing).

so expected result is:

addressid                            |streetNameCz    |streetNameEn    |streetNameFi    |
-------------------------------------|----------------|----------------|----------------|
e5c8c25c-f21e-47df-9172-7f3c7e52d669 |streetName_1_cz |streetName_1_en |streetName_1_fi |
bff096cc-4d4d-4b2e-aac2-bdc6ab659a72 |streetName_2_cz |                |streetName_2_fi |

How should do it? I tried to use crosstable, but it didn't work correctly because there are missing values for some languages, so i had result like:

addressid                            |streetNameCz    |streetNameEn    |streetNameFi    |
-------------------------------------|----------------|----------------|----------------|
e5c8c25c-f21e-47df-9172-7f3c7e52d669 |streetName_1_cz |streetName_1_en |streetName_1_fi |
bff096cc-4d4d-4b2e-aac2-bdc6ab659a72 |streetName_2_cz |streetName_2_fi |                |

which is not correct :-(.

This is select i used:

SELECT *
FROM crosstab(
  'select 
    "addressid"::uuid as rowid, 
    languagecode::text as attribute,
    name::text as value
from streetName
where languageCode in (''cz'', ''en'', ''fi'') 
order by 1, 2')
AS ct(row_name uuid, "streetNameCz" text, "streetNameEn" text, "streetNameFi" text);

Thanks for any advice.

Lange.

did you check this answer? https://stackoverflow.com/questions/3002499/postgresql-crosstab-query/11751905#11751905 — Juan Carlos Oropeza, Apr 10 '18 at 13:21
Hi, thanks for link, i didn't see it before .. i tested this approach too, but juan's solution seems to be much more quicker. — Lange, Apr 12 '18 at 11:42
You know is the same Juan ;). That option is also good specially if many columns. Also you can do dynamic pivot when you dont know the values. Remember accept the answer with the green tick if that help solve your problem — Juan Carlos Oropeza, Apr 12 '18 at 12:52

score 0 · Answer 1 · answered Apr 10 '18 at 13:23

if you don't want use crosstab you simple do aggregations:

  SELECT addressid,
         MAX( CASE WHEN languagecode = 'cz' THEN name END ) as lng_cz,
         MAX( CASE WHEN languagecode = 'en' THEN name END ) as lng_en,
         MAX( CASE WHEN languagecode = 'fi' THEN name END ) as lng_fi
  FROM YourTable
  GROUP BY addressid

Postrge: rows to columns

1 Answers1