Select query problem

jackps · January 19th, 2004, 03:21 AM

Dear all
I had a table which contain :

customer id,
charge type,
charge amount.

how i can create a select query which will show the amount of charge depend on different 'charge type', each type will show in individual column, so the result table will look something like this :

Cusotmer id Penalty Charge Penalty Interest
012625 150 20
216050 300 05

thx alot

Jack

pgtips · January 19th, 2004, 07:30 AM

You need a query like this to generate your required output - if you have other charge types, you'll need to add a further SUM(CASE...) for each one.

Code:

SELECT [customer id], 
    SUM(CASE [charge type] WHEN 'charge' THEN [charge amount] ELSE 0 END) AS [Penalty Charge],
    SUM(CASE [charge type] WHEN 'interest' THEN [charge amount] ELSE 0 END) AS [Penalty Interest]
FROM <<YourTable>>
GROUP BY [customer id]