Derived Table(View) is required

Question

I have the below table in database:

"ID Number"  "Balance Amount Type" "Balance Amount"
234             20                    94
234             21                    102
234             22                    100
212             20                    40
212             21                    50
212             22                    60

I want to create a below derived table(which is just like views in database) from the above table Universe having below fields :

"ID"    "BalAmount if Amount Type=20"     "BalAmount if Amount Type=21"   "BalAmount if Amount Type=22"
234         94                                102                             100
212         40                                50                              60

Can you please help me in writing SQL for this derived table, (Database is DB2) ?

Answer 1

A derived table is nothing more than a regular SELECT:

SELECT
    ID,
    MAX(CASE WHEN amount_type=20 THEN balamount END) type_20_amt,
    MAX(CASE WHEN amount_type=20 THEN balamount END) type_21_amt,
    MAX(CASE WHEN amount_type=20 THEN balamount END) type_22_amt
FROM
    table
GROUP BY
    ID

EDIT added in response to comment: The max() function is required in order to put all values on one row. Using the sample data from the question, a query without max() would produce:

id      type_20_amt    type_21_amt    type_22_amt
--      -----------    -----------    -----------
234              94
234                            102
234                                           100
212              40
212                             50
212                                            60

Using max(), however, puts them on one row:

id      type_20_amt    type_21_amt    type_22_amt
--      -----------    -----------    -----------
234              94            102            100
212              40             50             60