[SQL] SQL query to display like this
I have a table with records like this: Date EmpIDADCode ADAmount - 01-Jul-07101 GPF150 01-Jul-07102 GPF.ADV100 01-Jul-07103 GPF200 01-jul-07104 GPF300 I want to show results like this using a single SQL query: Date EmpID GPFGPF.ADV - 01-Jul-07 101150 0 01-Jul-07 1020100 01-Jul-07 103200 0 01-Jul-07 104300 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 GPFGPF.ADV - 01-Jul-07 1010 0 01-Jul-07 1011500 01-Jul-07 1020 100 01-Jul-07 1030 0 01-Jul-07 10310000 01-Jul-07 1030 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. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] SQL query to display like this
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 GPFGPF.ADV - 01-Jul-07 1010 0 01-Jul-07 1011500 01-Jul-07 1020 100 01-Jul-07 1030 0 01-Jul-07 10310000 01-Jul-07 1030 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 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
