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-    

LVL 18
John Gates, CISSPSecurity ProfessionalAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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

0
 
TimCotteeConnect With a Mentor 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

0
 
TimCotteeCommented:
AngelIII, surely adding null values will cause a null result.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
@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)
0
All Courses

From novice to tech pro — start learning today.