CODEWITHSUNDEEP
sqldb2
Ardia
Ardia

Reputation: 89

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

Upvotes: 0

Views: 305

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271131

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.

Upvotes: 2

Related Questions