The problem with case statements is that usually each one takes up a row for itself and then assigns a NULL value for the next case statement in the cell right next to it.
Here is an example of a query with 2 case statements that behaves that way:
SELECT CASE WHEN ( ct.type_name = ‘manager’ )
THEN c.fullname
ELSE ” END AS ‘Managers’,
CASE WHEN ( ct.type_name = ’employee’)
THEN c.fullname
ELSE ” END AS ‘Employees’,
from Contact AS c
LEFT JOIN Contact_type AS ct ON c.ref_type_id = ct.ID
WHERE ct.id is not null
Output will look like this:
———————–
John Smith | NULL
NULL | Mike Lee
But we want the output to look like this instead:
———————–
John Smith | Mike Lee
How to fix:
SELECT
MAX(CASE rtt.us_code WHEN ‘s’ THEN rtt.name END) AS input_tax_name,
MAX(CASE rtt.us_code WHEN ‘r’ THEN rtt.name END) AS output_tax_name,
MAX(CASE rtt.us_code WHEN ‘s’ THEN rtt.acc_id END) AS input_tax_rate,
MAX(CASE rtt.us_code WHEN ‘r’ THEN rtt.acc_id END) AS output_tax_rate
FROM supplier_item si
INNER JOIN ret_tx_type rtt
ON si.ret_tx_type_id = rtt.ret_tx_type_id
GROUP BY ??? /* here you should supply a column, presumably in ‘si’, that is
common to both of the related ‘r’- and ‘s’-tax records */
Another example:
with case when sum(value)
sum(CASE WHEN ltr.LookupName IN (‘Distribution’
, ‘Distribution – Quarterly’
, ‘Distribution – Refund Prior WH’
, ‘Distribution – Transferred’
, ‘Foreign Partner Withholding – Estimated’
, ‘Foreign Partner Withholding – Refund’
, ‘GP Catch-up’)
THEN (trn.Value) ELSE NULL END) ‘Distributions’
, sum(CASE WHEN ltr.LookupName IN (‘Return of Capital Sale/Refi-No GP ‘
, ‘Distribution – Sale Proceeds’
, ‘Distribution – Refinancing’)
THEN (trn.Value) ELSE NULL END) ‘Return of Capital’