CODEWITHSUNDEEP
sqloracle-databasepivot
Taoqir
Taoqir

Reputation: 189

How to pivot 1 column into two in SQL (and grouping the results by ID)

I have a table has a type column, and a value column. I would like to pivot to return all the types (I have 2 types MAIN_INT and PRINCIPAL) into a single row, with the column entitled the Type.

This is the source Table:

Account_No      Component Name   Amount Due 
002ENTA00000169 MAIN_INT         338.13
002ENTA00000169 MAIN_INT         308.65
002ENTA00000169 MAIN_INT         289.1
002ENTA00000169 MAIN_INT         250.36
002ENTA00000169 MAIN_INT         227.47
002ENTA00000169 MAIN_INT         196.08
002ENTA00000169 MAIN_INT         130.75 
002ENTA00000169 PRINCIPAL        1100.21
002ENTA00000169 PRINCIPAL        1154.28    
002ENTA00000169 PRINCIPAL        1173.83
002ENTA00000169 PRINCIPAL        1212.57
002ENTA00000169 PRINCIPAL        1235.46    
002ENTA00000169 PRINCIPAL        1266.85    
002ENTA00000169 PRINCIPAL        1304.33

This is what I want to see

ACCOUNT_No          MAIN_INT     PRINCIPAL  
002ENTA00000169     338.13       1100.21    
002ENTA00000169     308.65       1154.28    
002ENTA00000169     289.1        1173.83    
002ENTA00000169     250.36       1212.57    
002ENTA00000169     227.47       1235.46    
002ENTA00000169     196.08       1266.85    
002ENTA00000169     158.6        1304.33    
002ENTA00000169     130.75       1332.18    
002ENTA00000169     93.77        1369.16    
002ENTA00000169     62.11        1400.82    
002ENTA00000169     26.59        1043.23

Upvotes: 0

Views: 111

Answers (1)

Nick Krasnov
Nick Krasnov

Reputation: 27261

As I've understood you just want to separate data without aggregation(I didn't notice any in you example). 

 -- example of data from your question 
SQL> with t1(Account_No ,Component_Name, Amount_Due ) as
  2  (
  3    select '002ENTA00000169', 'MAIN_INT',  338.13  from dual union all
  4    select '002ENTA00000169', 'MAIN_INT',  308.65  from dual union all
  5    select '002ENTA00000169', 'MAIN_INT',  289.1   from dual union all
  6    select '002ENTA00000169', 'MAIN_INT',  250.36  from dual union all
  7    select '002ENTA00000169', 'MAIN_INT',  227.47  from dual union all
  8    select '002ENTA00000169', 'MAIN_INT',  196.08  from dual union all
  9    select '002ENTA00000169', 'MAIN_INT',  130.75  from dual union all
 10    select '002ENTA00000169', 'PRINCIPAL', 1100.21 from dual union all
 11    select '002ENTA00000169', 'PRINCIPAL', 1154.28 from dual union all
 12    select '002ENTA00000169', 'PRINCIPAL', 1173.83 from dual union all
 13    select '002ENTA00000169', 'PRINCIPAL', 1212.57 from dual union all
 14    select '002ENTA00000169', 'PRINCIPAL', 1235.46 from dual union all
 15    select '002ENTA00000169', 'PRINCIPAL', 1266.85 from dual union all
 16    select '002ENTA00000169', 'PRINCIPAL', 1304.33 from dual
 17  )  
  -- actual query

 18  select Account_No
 19       , MAIN_INT
 20       , PRINCIPAL
 21    from (
 22           select t.Account_No ,Component_Name, Amount_Due
 23                , row_number() over(partition by Account_No, Component_Name order by Account_No, Component_Name ) rn
 24             from t1 t
 25         )
 26  pivot(
 27    min(Amount_Due)
 28      for Component_Name in ('MAIN_INT' as MAIN_INT , 'PRINCIPAL' as PRINCIPAL)
 29  )
 30  order by rn
 31  ;

Result:

 ACCOUNT_NO        MAIN_INT  PRINCIPAL
--------------- ---------- ----------
002ENTA00000169     338,13    1100,21
002ENTA00000169     308,65    1154,28
002ENTA00000169      289,1    1173,83
002ENTA00000169     250,36    1212,57
002ENTA00000169     227,47    1235,46
002ENTA00000169     196,08    1266,85
002ENTA00000169     130,75    1304,33

Upvotes: 1

Related Questions