CODEWITHSUNDEEP
sql
PhilHQ
PhilHQ

Reputation: 58

Conditional SELECT depending on a set of rules

I need to get data from different columns depending on a set of rules and I don't see how to do it. Let me illustrate this with an example. I have a table:

ID   ELEM_01   ELEM_02    ELEM_03
---------------------------------
1     0.12       0          100
2     0.14       5          200
3     0.16       10         300
4     0.18       15         400
5     0.20       20         500

And I have a set of rules which look something like this:

P1Z: ID=2 and ELEM_01

P2Z: ID=4 and ELEM_03

P3Z: ID=4 and ELEM_02

P4Z: ID=3 and ELEM_03

I'm trying to output the following:

P1Z    P2Z    P3Z    P4Z
------------------------
0.14   400    15     300

I'm used to much simpler queries and this is a bit above my level. I'm getting mixed up by this problem and I don't see a straightforward solution. Any pointers would be appreciated.

EDIT Logic behind the rules: the table contains data about different aspects of a piece of equipment. Each combination of ID/ELEM_** represents the value of one aspect of the piece of equipment. The table contains all values of all aspects, but we want a row containing data on only a specific subset of aspects, so that we can output in a single table the values of a specific subset of aspects for all pieces of equipment.

Upvotes: 1

Views: 62

Answers (1)

D Stanley
D Stanley

Reputation: 152511

Assuming that each column is numeric and ID is unique you could do:

SELECT
  SUM(CASE WHEN ID = 2 THEN ELEM_01 END) AS P1Z,
  SUM(CASE WHEN ID = 4 THEN ELEM_03 END) AS P2Z,
  SUM(CASE WHEN ID = 4 THEN ELEM_02 END) AS P3Z,
  SUM(CASE WHEN ID = 3 THEN ELEM_03 END) AS P4Z
...

Upvotes: 2

Related Questions