Thread: SQL query to display like this
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.
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