Simplify simple self-join

Question

I have a table

id|level|name

Level

maybe 1,2 or 3

what i want to get:

id|lvl1name|lvl2name|lvl3name

I am 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 he is slow slow!

There must be a better way to do this. please, help

for this example, I am using postgres, but I would be glad to know that it does not depend on the database function.

I cannot write procedures (read-only access) and I select this from the view.

Answer 1

Using:

  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 .