Message-ID: <46CEC2A5.9090706@archonet.com>
Date: Fri, 24 Aug 2007 12:36:05 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.0 (X11/20070326)
MIME-Version: 1.0
To: RPK <rpk.general@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL query to display like this
References: <12310093.post@talk.nabble.com>
In-Reply-To: <12310093.post@talk.nabble.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

RPK wrote:
> 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. 

No it's not. It's showing 2 records for empid=101, 3 for empid=103 but 
only 1 for empid=102. That says to me you have multiple rows in your 
table for each (date,empid) combination.

If you want to eliminate them, either filter on AdCode or perhaps SUM() 
your GPF/GPF.ADV colums.

-- 
   Richard Huxton
   Archonet Ltd