Multiple Rows Values Into A Single Row

I have a requirement in oracle SQL where the multiple rows are to be converted into single row. Here is the example : Empid Ele_name Inp_name Inp_Value EntryId Start_date

Solution 1:

You need to do some more work before you can pivot like that, because pivoting takes row data and makes it into column names, but none of your row data is 1, 2, 3, 4... to use as a column name (inp_value1 <-- the 1 here)

You can do this, which is probably easier to understand:

SELECT
  Empid,
  Ele_name,
  MAX(CASEWHEN rown =1THEN Inp_name END) as Inp_name1,
  MAX(CASEWHEN rown =1THEN Inp_value END) as Inp_Value1,
  MAX(CASEWHEN rown =2THEN Inp_name END) as Inp_name2,
  MAX(CASEWHEN rown =2THEN Inp_value END) as Inp_Value2,
  MAX(CASEWHEN rown =3THEN Inp_name END) as Inp_name3,
  MAX(CASEWHEN rown =3THEN Inp_value END) as Inp_Value3,
  MAX(CASEWHEN rown =4THEN Inp_name END) as Inp_name4,
  MAX(CASEWHEN rown =4THEN Inp_value END) as Inp_Value4,
  MAX(CASEWHEN rown =5THEN Inp_name END) as Inp_name5,
  MAX(CASEWHEN rown =5THEN Inp_value END) as Inp_Value5,
  MAX(CASEWHEN rown =6THEN Inp_name END) as Inp_name6,
  MAX(CASEWHEN rown =6THEN Inp_value END) as Inp_Value6,
  EntryId,     
  Start_date,      
  End_Date
FROM
  (SELECT t2.*, ROW_NUMBER() OVER(PARTITIONBY EmpId, Ele_name ORDERBY1) as rown FROM t2) d
GROUPBY 
  Empid,
  Ele_name, 
  EntryId,     
  Start_date,      
  End_Date

• ps; why specify name3/4/5/6 in your expected output if they're all null? If the data will never have more than 2 rows per empid/ele_name pair then you can just write null as input_name3.. and so on
• pps: i called my table t2 - edit your name into the query
• ppps; I don't know if the column "end date" really has a space in the name, I called mine with an underscore

Or you can pivot like this (harder to understand but more compact):

SELECT
  Empid,
  Ele_name,
  pvt.*,
  EntryId,     
  Start_date,      
  End_Date
FROM
  (SELECT t2.*, ROW_NUMBER() OVER(PARTITIONBY EmpId, Ele_name ORDERBY1) as rown
   FROM t2) d
PIVOT( 
  MAX(inp_name) as inp_name, 
  MAX(inp_value) as inp_value 
  FOR rown in (1,2,3,4,5,6) 
) pvt

but the columns will come out of the pvt.* with names as 1_inp_name, 1_inp_value .. You'll have to use AS to rename them