Flatten out key-value data into a row

flatten.sql

-- Imagine the following table...
--  --------------------
-- |ID|NAME|VAL         |
-- |==|====|============|
-- |A |KEY1|1.0000000000|
-- |A |KEY2|2.0000000000|
-- |B |KEY1|3.0000000000|
-- |B |KEY2|4.0000000000|
-- |B |KEY3|5.0000000000|
-- |C |KEY1|6.0000000000|
-- |D |KEY2|7.0000000000|
--  --------------------


-- You can flatten out by INNER JOINing. For example...
SELECT a.ID, a.VAL, b.VAL FROM
  (SELECT * FROM TEST_TBL WHERE NAME='KEY1') a
    INNER JOIN
  (SELECT * FROM TEST_TBL WHERE NAME='KEY2') b
    ON a.ID=b.ID;
-- Will result in...
--  ----------------------------
-- |ID|VAL         |VAL         |
-- |==|============|============|
-- |A |1.0000000000|2.0000000000|
-- |B |3.0000000000|4.0000000000|
--  ----------------------------
-- Note that 'A' and 'B' are there because they both contain an entry for 'KEY1' and 'KEY2, but...
--  'C' is missing because it's missing 'KEY2'
--  'D' is missing becuase it's missing 'KEY1'


-- So what if we want to include 'C' and 'D'? The subqueries in our original query need to be changed to include all IDs. So for
-- example, for 'KEY1', LEFT JOIN the all IDs with the IDs that have 'KEY1'...
SELECT a.ID, b.VAL FROM
  (SELECT DISTINCT ID FROM TEST_TBL) a -- set of all IDs
    LEFT JOIN
  (SELECT ID, VAL FROM TEST_TBL WHERE NAME='KEY1') b -- set of IDs/VALs that contain 'KEY1'
    ON a.ID = b.ID;
-- Will result in...
--  ---------------
-- |ID|VAL         |
-- |==|============|
-- |A |1.0000000000|
-- |B |3.0000000000|
-- |C |6.0000000000|
-- |D |<null>      |
--  ---------------


-- Once you substitute these new subqueries in...
SELECT a.ID, a.VAL, b.VAL FROM
  (SELECT aa.ID, ab.VAL FROM (SELECT DISTINCT ID FROM TEST_TBL) aa LEFT JOIN (SELECT ID, VAL FROM TEST_TBL WHERE NAME='KEY1') ab ON aa.ID = ab.ID) a
    INNER JOIN
  (SELECT ba.ID, bb.VAL FROM (SELECT DISTINCT ID FROM TEST_TBL) ba LEFT JOIN (SELECT ID, VAL FROM TEST_TBL WHERE NAME='KEY2') bb ON ba.ID = bb.ID) b
    ON a.ID=b.ID;
-- You'll get the correct results..
--  ----------------------------
-- |ID|VAL         |VAL         |
-- |==|============|============|
-- |A |1.0000000000|2.0000000000|
-- |B |3.0000000000|4.0000000000|
-- |C |6.0000000000|<null>      |
-- |D |<null>      |7.0000000000|
--  ----------------------------