making simple self join more efficent

pivot, postgresql, sql

Solution

Use:

  SELECT t.id,
         MAX(CASE WHEN t.level = 1 THEN t.name END) AS level1name,
         MAX(CASE WHEN t.level = 2 THEN t.name END) AS level2name,
         MAX(CASE WHEN t.level = 3 THEN t.name END) AS level3name
    FROM YOUR_TABLE t
GROUP BY t.id

If you need this to be dynamic, you need to use dynamic SQL.

Problem

I have a table ``` id|level|name ``` level can be 1,2 or 3 what I want to get is: ``` id|lvl1name|lvl2name|lvl3name ``` I'm using the following query ``` SELECT L1."name" as lvl1name, L2."name" as lvl2name, L3."name" as lvl3name, L1.id FROM table as L1 JOIN table as L2 ON L1.id = L2.id JOIN table as L3 ON L2.id = L3.id WHERE L1.lvl='1' and L2.lvl='2' and L3.lvl='3'; ``` but it is soooooo slow! there must be a better way to do this. please help for this example I'm using postgres, but I'd be happy to learn some way that is not database feature dependant. I can't write procedures (read only access), and I select this from a view.

Original source