I would like the following pivot query to show value 0 instead of null,
x
SELECT
pi.employeeId,
pi.Id,
MAX(CASE
WHEN pi.category = 'Repayment' THEN pi.value
WHEN isnull(pi.category) = 1 then 0
-- ELSE 0
END) as 'Repayment',
MAX(CASE
WHEN pi.category = 'Salary' THEN pi.value
ELSE 0
END) as 'Salary',
MAX(CASE
WHEN pi.category = 'Allowance' THEN pi.value
ELSE 0
END) as 'Allowance'
FROM
payData pi
GROUP BY pi.employeeId , pi.Id ;
Output for the above is,
employeeId Id Repayment Salary Allowance
121 2 2000 15000 1000
122 2 null 20000 2000
Employee id 122 does not have a Repayment value so the desired output is,
employeeId Id Repayment Salary Allowance
121 2 2000 15000 1000
122 2 0 20000 2000
Advertisement
Answer
I don’t see the need for the second branch of the repayment case
. If you want 0
when the category is not available, just else 0
:
SELECT
employeeId,
Id,
MAX(CASE WHEN category = 'Repayment' THEN value ELSE 0 END) as Repayment,
MAX(CASE WHEN category = 'Salary' THEN value ELSE 0 END) as Salary,
MAX(CASE WHEN category = 'Allowance' THEN value ELSE 0 END) as Allowance
FROM payData pi
GROUP BY employeeId, Id;
Notes:
Don’t use single quotes for identifiers! They should be used for literal strings only, as specified in ANSI SQL and supported in all databases.
You have a mono-table query, so prefixing all column names is not mandatory