Select a column value
I have a table of items identified by their unique ID, as well as some properties.
To make the table readable, the property is always stored in the "prop_value" column, and its type is stored in the "prop_type" column
To keep it simple, I only used three properties, there are actually 12 of them.
+----+-------------+-------------+
+ ID + prop_type + prop_value +
+----+-------------+-------------+
+ 1 + prop1 + foo +
+----+-------------+-------------+
+ 1 + prop3 + toto +
+----+-------------+-------------+
+ 3 + prop2 + lorem +
+----+-------------+-------------+
The goal is to get all the properties of an element in a sigle string, the result is like:
+----+-------------+-------------+-------------+
+ ID + prop1 + prop2 + prop3 +
+----+-------------+-------------+-------------+
+ 1 + foo + NULL + toto +
+----+-------------+-------------+-------------+
+ 2 + NULL + lorem + NULL +
+----+-------------+-------------+-------------+
In the beginning I used a very ugly solution:
select prop1 from myTable where id = 1
select prop2 from myTable where id = 1
...
Now that I scale it down, it happens forever and I would like to do it in one request.
I've tried something like:
select a.prop_value, b.prop_value, c.prop_value from myTable a FULL JOIN myTable b FULL JOIN myTable c ...
But it seems to me that I am entering something more complex than it should be.
Is there a compact solution to achieve this? Thank you for your help.
source to share
You can use some conditional logic like statement CASE
as well as aggregate function to get the result:
select Id,
max(case when prop_type = 'prop1' then prop_value end) as Prop1,
max(case when prop_type = 'prop2' then prop_value end) as Prop2,
max(case when prop_type = 'prop3' then prop_value end) as Prop3
from myTable
group by Id;
This type of query rotates row values into columns.
source to share