We help IT Professionals succeed at work.

CASE STATEMENT HELP

I have a view that returns data in this fashion:

ID           FEE            Amount        
001        710            10.00
001        720            20.00


But I need to display the data as:

ID        R_fee      C_fee  
001      amount    amount


I know I should use a case statement for this.  Can anyone shed some light on how this should be done?

Thanks
-D-    

Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
so, FEE=710 has to go to column R_Fee, and FEE=720 has to go to C_fee?


SELECT ID
, SUM ( CASE WHEN FEE= 710 THEN amount else null END) R_Fee
, SUM ( CASE WHEN FEE= 720 THEN amount else null END) C_Fee
FROM yourtable
GROUP BY id

Open in new window

TimCotteeHead of Software Services
CERTIFIED EXPERT
Commented:
How about this.
Select 
  ID,
  Sum(Case When FEE=710 Then Amount Else 0 End) As R_Fee,
  Sum(Case When FEE=720 Then Amount Else 0 End) As C_Fee
From
  MyTable
Group By
  ID

Open in new window

TimCotteeHead of Software Services
CERTIFIED EXPERT

Commented:
AngelIII, surely adding null values will cause a null result.
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
another option

SELECT ID, (SELECT amount FROM urTable WHERE id = a.id AND fee = 710) R_fee
,(SELECT amount FROM urTable WHERE id = a.id AND fee = 720) c_fee
FROM urTable a
GROUP BY ID
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
@TomCottee:
SUM ( ... ) with NULL value will NOT cause a null result, unless all the values for the grouped row are null for that column.

you might have confused with  something + NULL (with both arithmetic and string) will indeed result in NULL (unless the CONCAT_NULL_YIELD_NULL is set to OFF)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.