CODEWITHSUNDEEP
sqloracle-database
Maxime
Maxime

Reputation: 1345

Select using the value of a column

I have a table of items, identified by their unique ID, along with some properties.

To keep the table readable, the property is always stored in the column 'prop_value' and its type is stored in the column 'prop_type'

To simplify, I have used only three properties, in fact, there is 12.

+----+-------------+-------------+
+ ID + prop_type   +  prop_value +
+----+-------------+-------------+
+ 1  +    prop1    +   foo       +
+----+-------------+-------------+
+ 1  +    prop3    +   toto      +
+----+-------------+-------------+
+ 3  +    prop2    +  lorem      +
+----+-------------+-------------+

The aim is to get all the properties for an item in a sigle row, a result such as:

+----+-------------+-------------+-------------+
+ ID +     prop1   +    prop2    +      prop3  +
+----+-------------+-------------+-------------+
+ 1  +    foo      +   NULL      +    toto     +
+----+-------------+-------------+-------------+
+ 2  +    NULL     +   lorem     +    NULL     +
+----+-------------+-------------+-------------+

At 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 am scaling this up, it is taking forever, and I would like to make it in one query.

I have 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 feels like I'm am going into something way more complicated than it needs to be.

Is there a compact solution to achieve this? Thank you for your help.

Upvotes: 3

Views: 73

Answers (2)

ollie
ollie

Reputation: 1009

You can use some conditional logic, such as a CASE statement, along with an 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 pivots the row values into columns.

Upvotes: 0

MT0
MT0

Reputation: 168806

Use a PIVOT:

SELECT *
FROM   your_table
PIVOT  ( MAX( prop_value ) FOR prop_type IN (
  'prop1' AS prop1,
  'prop2' AS prop2,
  'prop3' AS prop3
) )

Upvotes: 3

Related Questions