SQL: join within same table with different 'where' clause

Ok, so the problem I'm facing is this, I have a table with 3 columns : ID, Key and Value.

ID | Key | Value
================
1  |  1  |  ab
1  |  2  |  cd
1  |  3  |  ef
2  |  1  |  gh
2  |  2  |  ij
2  |  3  |  kl

Now I want to select the value of Keys 1 & 3 for all IDs, the return should be like this

ID |  1  | 2
================
1  |  ab |  ef
2  |  gh |  kl

So per ID 1 row containing the Values for Keys 1 & 3.

I tried using 'join' but since I need to use multiple where clauses I can't figure out how to get this to work ..

For Oracle 8i+, use:

  SELECT t.id,
         MAX(CASE WHEN t.key = 1 THEN t.value ELSE NULL END) AS "1",
         MAX(CASE WHEN t.key = 2 THEN t.value ELSE NULL END) AS "2"
    FROM TABLE t
GROUP BY t.id

For Oracle versions prior, swap the CASE out for DECODE syntax. Oracle didn't add the PIVOT syntax until 11g.

Without using pivot queries, you could also join with a subquery, as follows:

SELECT     t.id, MAX(key_1.value) AS '1', MAX(key_3.value) AS '2'
FROM       tb t
INNER JOIN (SELECT id, value FROM tb WHERE `key` = 1) key_1 ON (key_1.id = t.id)
INNER JOIN (SELECT id, value FROM tb WHERE `key` = 3) key_3 ON (key_3.id = t.id)
GROUP BY   t.id;

Test Case (in MySQL):

CREATE TABLE tb (`id` int, `key` int, `value` char(2));
INSERT INTO tb VALUES (1, 1, 'ab');
INSERT INTO tb VALUES (1, 2, 'cd');
INSERT INTO tb VALUES (1, 3, 'ef');
INSERT INTO tb VALUES (2, 1, 'gh');
INSERT INTO tb VALUES (2, 2, 'ij');
INSERT INTO tb VALUES (2, 3, 'kl');

Result:

+------+------+------+
| id   | 1    | 2    |
+------+------+------+
|    1 | ab   | ef   |
|    2 | gh   | kl   |
+------+------+------+
2 rows in set (0.04 sec)

Why can't you just do three queries?

If I understand you correctly, your going to have to use a union join.

SELECT ID, VALUE AS v1, S.v2
FROM TABLE
WHERE KEY = 1
JOIN (SELECT ID, VALUE AS v2 FROM TABLE WHERE Key =3) AS S ON TABLE.ID = S.ID

If you need more, add a join for each where...