Ardia Ardia - 3 months ago 7
SQL Question

Combining Queries into one Display Line in DB2

The relevant (greatly abbreviated) part of my SQL:

Select
A.Client_ID,
,CASE
WHEN FN.FLD_CD = 1
THEN
FN.AMOUNT
ELSE
NULL
END AS "LN1"
,CASE
WHEN FN.FLD_CD = 2
THEN
FN.AMOUNT
ELSE
NULL
END AS "LN2"
,CASE
WHEN FN.FLD_CD = 3
THEN
FN.AMOUNT
ELSE
NULL
END AS "LN3"
(...and so on)
FROM
ASSESSMENT A,
FINANCE FN
WHERE
A.CLIENT_ID = FN.CLIENT_ID
AND FN.FLD_CD IN ( '1', '2', '3')
(other irrelevant stuff)
;


This displays the results from the Query on 3 separate lines (one for each field code). How do I get it to display all on one line?

For example, the Results are:

Client_ID LN1 LN2 LN3
10001 20 - -
10001 - 30 -
10001 - - 40


but I would like it like this:

Client ID LN1 LN2 LN3
10001 20 30 40

Answer

Use conditional aggregation. Assuming one value per client per code:

SELECT A.Client_ID,
       MAX(CASE WHEN FN.FLD_CD = 1 THEN FN.AMOUNT END) as LN1,
       MAX(CASE WHEN FN.FLD_CD = 2 THEN FN.AMOUNT END) as LN2,
       MAX(CASE WHEN FN.FLD_CD = 3 THEN FN.AMOUNT END) as LN3
FROM ASSESSMENT A JOIN
     FINANCE FN
     ON A.CLIENT_ID = FN.CLIENT_ID
WHERE FN.FLD_CD IN ('1', '2', '3')
   (other irrelevant stuff)
GROUP BY A.CLIENT_ID;

Notes:

  • Never use commas in the FROM clause. Always use explicit JOIN syntax.
  • The solution to your query is the GROUP BY clause along with the aggregation functions in the SELECT clause.
  • You might want SUM() instead of MAX() if multiple codes exist with the same value for a client.
  • As the query is written, you don't need a JOIN (just use f.CLIENT_ID for the SELECT and GROUP BY. I assume the JOIN is needed for other stuff not in the query.