How to go about a column with different values in

Question

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???

Answer 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