Search code examples
sqlpivotoracle12c

Use pivot to change rows to columns for sequencing data using sql

I have data in a view where the Col_Head column values are supposed to be column headers(they are constant for every sequence of data from 1-8.

And Value column entries need to come as row values.

I need to write some SQL that transposes/pivots rows from Col_Head as column headers.

Example:

enter image description here

Expected data:

enter image description here

Solution

  • If you had a specific Id for each person then you didn't need to create CTE, any way

    First, I create specific Id for each person like this via CTE: sp_id

       /*Create CTE*/
    With tempTable as
    (
    select  
     row_number() over( order by(select 0) ) row_num,
    *
    from myTable
    ),newTable as(
    
    select 
    case when (row_num %8)>0 then (row_num /8)+1 else (row_num /8) end sp_Id,
    *
    from tempTable 
    )  

  /*MainQuery*/  
    select 
    *
    from (select sp_id, Col_Header,[Value] from newTable )as temp 
    pivot
    (
    max([Value])
    for Col_Header in ([Emp name],[Emp Dept],[Emp Grade],[Emp class],[Emp Sal],[Emp manager],[Emp Date of join],[Emp documents])
    ) pivotTable

    result