-2

my table has the following strucuture

empno    empname    loan ref   amount
1        abc        123        100
1        abc        456        200.

i.e an employee can avail two loans(say car and scooter).

The output expected:

empno   empname      loan ref1   amt    loanref2    amt
1       abc           120        100    456         200

to avoid duplicate empno repeating. How to go about it in sql???

Suman08513
  • 11
  • 3
    How many duplicated rows can you have for each empno? If this is variable, you need a query with a variable number of columns, which is not possible. Also, have you tried anything? Please post your attempts and the issues you're having. – Aleksej Jan 09 '17 at 11:30
  • 3
    To start with: this is a horrible table design. What if you find empno 1 in one record with name 'abc' and in another with name 'efg'? Then, how many entries can you have for one employee? How many columns do you want to get? – Thorsten Kettner Jan 09 '17 at 11:30
  • Also, WHY do you want to avoid "duplicates" in the table? What is wrong with duplicates? They are perfectly valid, normal and standard in this kind of situation. Don't you have some real problems that need to be solved? –  Jan 09 '17 at 22:09
  • An employee can have variable number of recoveries. But we don't care about them now. My primary goal here is to get only data related to 2 recoveries(i.e car and scooter loans). Those 2 recoveries has distinct reference numbers and distinct recovery codes. Basically when I queried the database for every employee 2 records have been fetched when the employee has both the loans. I need to extract the result into an excel file and hand over it to end user. Hence to reduce duplicates is it possible to reduce 2 rows for a single employee to one row – Suman08513 Jan 11 '17 at 08:08
  • @ Thorsten Kettner : Design was like that only. So any solution for the above requirement? – Suman08513 Jan 11 '17 at 08:11
  • This is what I did : select * from table1 where payperiod='201612' and empno='123' and earningdeduction in('RY909C','RY941C') – Suman08513 Jan 11 '17 at 08:14
  • 2 rows with the above mentioned recovery codes have been fetched for that particular month for the mentioned employed – Suman08513 Jan 11 '17 at 08:16

1 Answers1

1

Concerning the previous comments on table design - there is, in fact, a redundancy in the table; you could store the empname in another table, which you would join with your table here to avoid that; every redundancy is a potential contradiction. However, if we have a table design optimised for querying and minimising necessary joins, it might be populated in a batch job from somewhere else, and then the design would be appropriate.

What you want to do here is often referred to as 'horizontal pivoting'. We lack some info here, so I'm assuming a maximum number of loans of 2. We need a mechanism that allows us to put data in col1 or col2, depending on whether it's the first or second row for the same empno. That's why we generate a sequence number. Finally, we use a SUM(CASE seq WHEN ...) expression in conjunction with a GROUP BY to reduce the number of rows and flatten the table.

Here goes:

-- first global table expression - the input table
-- The table could exist already, and then this would not be needed.
WITH foo(empno,empname,loanref,amount) AS (
          SELECT  1,'abc',123,100
UNION ALL SELECT  1,'abc',456,200
)
-- second global table expression - add sequence number
-- this needs to be in the query
,    foo_numbered AS (
SELECT
  -- need a number: 1 for the first, 2 for the second loan
  ROW_NUMBER() OVER(PARTITION BY empname ORDER BY loanref) AS seq
, *
FROM foo
)
SELECT
  empno
, empname
, MAX(CASE seq WHEN 1 THEN loanref END) AS loanref_1
, SUM(CASE seq WHEN 1 THEN amount END) AS amount_1
, MAX(CASE seq WHEN 2 THEN loanref END) AS loanref_2
, SUM(CASE seq WHEN 2 THEN amount END) AS amount_2
FROM foo_numbered
GROUP BY
  empno
, empname
;

Happy playing

Marco

marcothesane
  • 6,192
  • 1
  • 11
  • 21