How to transpose this table in postgresql

Question

I have this table:

 lnumber |    lname       |           bez_gem              
---------+----------------+------------------------------
       1 | name1          | Berg b.Neumarkt i.d.OPf.
       1 | name1          | Altdorf b.Nürnberg
       2 | name2          | Berg b.Neumarkt i.d.OPf.
       2 | name2          | Altdorf b.Nürnberg
       3 | name3          | Mainleus
       3 | name3          | Weismain
       4 | name4          | Weismain
       4 | name4          | Mainleus

The code for the query is:

WITH double AS (
SELECT
    partnumber,
    bez_gem
FROM accumulation a, municipality b
    WHERE ST_Intersects(a.geom, b.geom)
        AND EXISTS (
             SELECT 
                lnumber 
            FROM mun_more_than_once c 
                WHERE a.partnumber=c.lnumber)
        ORDER BY partnumber)

SELECT
    landslide.lnumber,
    lname,
    bez_gem
FROM double, landslide
    WHERE double.partnumber=landslide.lnumber
        ORDER BY lnumber

I want to transpose in this format

 lnumber |    lname       |           bez_gem1        |  bez_gem2   
---------+----------------+------------------------------------------------
       1 | name1          | Berg b.Neumarkt i.d.OPf.  | Altdorf b.Nürnberg
       2 | name2          | Berg b.Neumarkt i.d.OPf.  | Altdorf b.Nürnberg

I don't know if it can be accounted as a duplicate, but you are requesting exactly the opposite transformation of: http://stackoverflow.com/questions/22347587/concise-syntax-on-a-single-table-to-collapse-columns-using-union-all — guido, Mar 13 '14 at 11:37

score 1 · Answer 1 · edited May 23 '17 at 12:11

It depends. If you always have two bez_gem per lnumber, you can simply use:

SELECT lnumber, lname
     , min(bez_gem) AS bez_gem1
     , max(bez_gem) AS bez_gem2
FROM   test
GROUP  BY 1,2
ORDER  BY 1;

SQL Fiddle.

Note that the order of peers is undefined in your question. Collation rules (alphabetical order) decide in my example.

For an actual cross tabulation you would use the crosstab() function from the additional module tablefunc. But your table is missing a category name (no indication which row holds bez_gem1 and which bez_gem2). Explanation, details and links:
PostgreSQL Crosstab Query

score 0 · Accepted Answer · edited May 23 '17 at 11:57

SQLFiddle

Data

-- drop table if exists test;

create table test (lnumber int, lname varchar, bez_gem varchar);

    insert into test values

           (1 , 'name1'          , 'Berg b.Neumarkt i.d.OPf.'),
           (1 , 'name1'          , 'Altdorf b.Nürnberg'),
           (2 , 'name2'          , 'Berg b.Neumarkt i.d.OPf.'),
           (2 , 'name2'          , 'Altdorf b.Nürnberg'),
           (3 , 'name3'          , 'Mainleus'),
           (3 , 'name3'          , 'Weismain'),
           (4 , 'name4'          , 'Weismain'),
           (4 , 'name4'          , 'Mainleus'),
           (4 , 'name4'          , 'XXMainleus')       
           ;

Query

select
  lnumber,
  lname,
  max(case when rn = 1 then bez_gem end) as bez_gem1,
  max(case when rn = 2 then bez_gem end) as bez_gem2,
  max(case when rn = 3 then bez_gem end) as bez_gem3   
from
  (
  select
    *,
    row_number() over(partition by lname) rn
  from
    test
  ) a
 group by
  lnumber,
  lname

Result

1;name1;Berg b.Neumarkt i.d.OPf.;Altdorf b.Nürnberg;
2;name2;Berg b.Neumarkt i.d.OPf.;Altdorf b.Nürnberg;
3;name3;Mainleus;Weismain;
4;name4;Weismain;Mainleus;XXMainleus

Old Answer

If you have only two possible rows for every lnumber (you should add this important info to your question), you can simply use min and max:

WITH double AS (
SELECT
    partnumber,
    bez_gem
FROM accumulation a, municipality b
    WHERE ST_Intersects(a.geom, b.geom)
        AND EXISTS (
             SELECT 
                lnumber 
            FROM mun_more_than_once c 
                WHERE a.partnumber=c.lnumber)
        ORDER BY partnumber)

SELECT
    landslide.lnumber,
    lname,
    min(bez_gem) as bez_gem1,
    max(bez_gem) as bez_gem2
FROM double, landslide
    WHERE double.partnumber=landslide.lnumber
group by
    landslide.lnumber,
    lname
ORDER BY lnumber

If you have possibly more than two rows for every lnumber and you really need crosstab, there is a lot of questions regarding crosstab in PostgreSQL on SO (example). As an alternative you can try the following approach.

Because this is one-time analysis, you can easily get maximum number of unique bez_gem values:

select 
  landslide.lnumber, 
  count(distinct bez_gem) cnt 
from 
  <<some_data>> 
group by 
  landslide.lnumber 
order by 
   cnt desc limit 1

Then you can use:

select
 landslide.lnumber,
 lname,
 max(case when rn=1 then bez_gem end) as bez_gem1,
 max(case when rn=2 then bez_gem end) as bez_gem2,
 max(case when rn=3 then bez_gem end) as bez_gem3,
 max(case when rn=4 then bez_gem end) as bez_gem4,
 max(case when rn=5 then bez_gem end) as bez_gem5,
 ... up to cnt ...
from(
  select
   landslide.lnumber,
   lname,
   bez_gem,
   row_number() over(partition by landslide.lnumber) rn
  from
   <<some_data>>
 ) a
group by
  landslide.lnumber,
  lname

For your data and 5 possible values it would look like:

WITH double AS (
SELECT
    partnumber, bez_gem
FROM 
  accumulation a, municipality b
WHERE 
  ST_Intersects(a.geom, b.geom)
  AND EXISTS (
              SELECT lnumber 
              FROM mun_more_than_once c 
              WHERE a.partnumber=c.lnumber)
ORDER BY 
  partnumber
)

select
  landslide.lnumber,
  lname,
  max(case when rn=1 then bez_gem end) as bez_gem1,
  max(case when rn=2 then bez_gem end) as bez_gem2,
  max(case when rn=3 then bez_gem end) as bez_gem3,
  max(case when rn=4 then bez_gem end) as bez_gem4,
  max(case when rn=5 then bez_gem end) as bez_gem5
from (
  select
      landslide.lnumber,
      lname,
      bez_gem,
      row_number() over(partition by landslide.lnumber) rn
  from 
      double, landslide
  where 
    double.partnumber=landslide.lnumber
) a       
group by
  landslide.lnumber,
  lname

How many rows? Does this need to be dynamic (underlying data are changing and so should number of columns) or is this one-time exercise? — Tomas Greif, Mar 13 '14 at 12:46
This is a on-time exercise. I've extended the table in my question. I tried the cross tab (like in the exmaple you gave me), but I have a lot of distinct municipalities and I don't know how to use the `VALUE` expression in this case. — Stefan, Mar 13 '14 at 12:54
I added another possible solution, without using crosstab extension. I guess this will slow, but should work. — Tomas Greif, Mar 13 '14 at 14:24
When I use your edited query, for every lnumber (lname) there are three bez_gem with the same values for every row and to many results (526 instead of 66). You forgot an alias on the subquery and lname was ambiguous. — Stefan, Mar 13 '14 at 15:06
Yeah, it is kind of difficult to write correct sql without test data. Added working solution, added SQLFiddle example. — Tomas Greif, Mar 13 '14 at 15:19
Finally it works when I use the row_number subquery like a `WITH` query. After that I can use the `CASE WHEN` expressions. I'm wondering if there a problems using `row_number` in subqueries like in your approach, in general? — Stefan, Mar 13 '14 at 15:20
`row_number` should work fine in any subquery. However, it does not work together with `group by` clause. — Tomas Greif, Mar 13 '14 at 15:22

How to transpose this table in postgresql

2 Answers2