Click here to Skip to main content
15,886,362 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
How to set multiple case statements (3 or more) with alias for each in sql server 2005?

SQL
(CASE WHEN E.Mode ='U' THEN ISNULL(SUM(ES.SALARY),0)) AS PBFSALARY
                ELSE ''
           END) AS PBFSALARY,
           (CASE WHEN E.Mode ='C' THEN ISNULL(SUM(ES.SALARY),0)
                ELSE ''
           END) AS CREDITSALARY,
            (CASE WHEN E.Mode ='M' THEN ISNULL(SUM(ES.SALARY),0)
                ELSE ''
           END) AS DEBITSALARY,


Getting error conversion of varchar to numeric issue.
Posted
Updated 12-Mar-14 18:23pm
v2

SQL
SELECT 
	ISNULL(SUM(PBFSALARY),0) PBFSALARY,
	ISNULL(SUM(CREDITSALARY),0) CREDITSALARY,
	ISNULL(SUM(DEBITSALARY),0) DEBITSALARY
FROM
(
	SELECT
		(CASE WHEN E.Mode ='U' THEN ES.SALARY ELSE 0 END) AS PBFSALARY,
		(CASE WHEN E.Mode ='C' THEN ES.SALARY ELSE 0 END) AS CREDITSALARY,
		(CASE WHEN E.Mode ='M' THEN ES.SALARY ELSE 0 END) AS DEBITSALARY
	FROM
		yourTable
)
AS a

Happy Coding!
:)
 
Share this answer
 
Adapt the following to your need instead:
SQL
select
(select SUM(isnull(salary, 0)) FROM table1
where  mode='u') AS U
,
(select SUM(isnull(salary, 0)) FROM table1
where  mode='c') AS c
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900