Message-ID: <12310093.post@talk.nabble.com>
Date: Fri, 24 Aug 2007 03:42:55 -0700 (PDT)
From: RPK <rpk.general@gmail.com>
To: pgsql-sql@postgresql.org
Subject: SQL query to display like this
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit


I have a table with records like this:

Date              EmpID    ADCode      ADAmount
-------------------------------------------------
01-Jul-07        101      GPF            150
01-Jul-07        102      GPF.ADV        100
01-Jul-07        103      GPF            200
01-jul-07        104      GPF            300

I want to show results like this using a single SQL query:

Date      EmpID      GPF        GPF.ADV
-------------------------------------------------
01-Jul-07  101        150              0
01-Jul-07  102        0                100
01-Jul-07  103        200              0
01-Jul-07  104        300              0


I tried:

select PaySlipDate,EmpID,
case ADCode when 'GPF' then ADAmount else 0 end GPF,
case ADCode when 'GPF.ADV' then ADAmount else 0 end 'GPF.ADV'
from EmpSalaryRecord

It is showing like this:

Date      EmpID      GPF        GPF.ADV
-----------------------------------------
01-Jul-07  101        0          0
01-Jul-07  101        150        0
01-Jul-07  102        0          100
01-Jul-07  103        0          0
01-Jul-07  103        1000        0
01-Jul-07  103        0          1000

It is showing multiple records of each employee for each date. First a
record with GPF and GPF.ADV both zero and then records with values. I want a
single record for each date and employee.
-- 
View this message in context: http://www.nabble.com/SQL-query-to-display-like-this-tf4322876.html#a12310093
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.