Pivot Table in Oracle

Try this one

SELECT 
    CONTRACTNUM,
    POSITIONDESC,
    RATE1, Change_date1,
    RATE2, Change_date2,
    RATE3, Change_date3,
    RATE4, Change_date4,
    RATE5, Change_date5
FROM (
    SELECT 
        CONTRACTNUM,
        POSITIONDESC,
        RATE801,
        STARTDATE,
        ROW_NUMBER() OVER (PARTITION BY CONTRACTNUM, POSITIONDESC ORDER BY STARTDATE) AS RN
    FROM 
        BI_HZ_ETL.LEM_CNRLEMCRAFTRATE
    WHERE 
        CONTRACTNUM = 'A689'
)
PIVOT (
    MAX(RATE801) AS Rate,
    MAX(STARTDATE) AS Change_date
    FOR RN IN (1 AS RATE1, 2 AS RATE2, 3 AS RATE3, 4 AS RATE4, 5 AS RATE5)
) AS PIVOTED
ORDER BY 
    CONTRACTNUM,
    POSITIONDESC;

Good Morning Prasad Raveendran,

This is the error I got,

Thanks lots for your time and effort

you can utilize a subquery to pre-aggregate the data before pivoting. Here's the modified query:

SELECT 
    CONTRACTNUM,
    POSITIONDESC,
    RATE1, Change_date1,
    RATE2, Change_date2,
    RATE3, Change_date3,
    RATE4, Change_date4,
    RATE5, Change_date5
FROM (
    SELECT 
        CONTRACTNUM,
        POSITIONDESC,
        RATE801,
        STARTDATE,
        ROW_NUMBER() OVER (PARTITION BY CONTRACTNUM, POSITIONDESC ORDER BY STARTDATE) AS RN
    FROM 
        BI_HZ_ETL.LEM_CNRLEMCRAFTRATE
    WHERE 
        CONTRACTNUM = 'A689'
)
PIVOT (
    MAX(RATE801) AS Rate,
    MAX(STARTDATE) AS Change_date
    FOR RN IN (1 AS 1, 2 AS 2, 3 AS 3, 4 AS 4, 5 AS 5)
)
ORDER BY 
    CONTRACTNUM,
    POSITIONDESC;

This version first assigns a row number to each record within the same contract number and position description. Then it pivots the data based on those row numbers. This should ensure that each rate and change date combination is correctly placed in its respective column.

Hello Prasad Raveendran

Its giving me this kind of out put

Same Rate and  same Date are repeating to column which is incorrect 

Also We need output in one row

Please, help me

Thanks for your time

Hello Jayraj,

Thanks for your time

I am getting error

Please

Hello Naimish,

Thanks for your time

I am getting below error

DataSource.Error: Oracle: ORA-06550: line 1, column 9: PLS-00103: Encountered the symbol "@" when expecting one of the following: begin function pragma procedure subtype type current cursor delete exists prior Details: DataSourceKind=Oracle DataSourcePath=rptprd01 Message=ORA-06550: line 1, column 9: PLS-00103: Encountered the symbol "@" when expecting one of the following: begin function pragma procedure subtype type current cursor delete exists prior ErrorCode=-2147467259

Hi,

To achieve the desired output format, you can use the Oracle SQL PIVOT function along with conditional aggregation to pivot the data based on the POSITIONDESC column. Here's an example query that can help you reformat the data:

SELECT *
FROM (
    SELECT CONTRACTNUM, POSITIONDESC, RATE801, STARTDATE,
           'Rate' || ROW_NUMBER() OVER (PARTITION BY CONTRACTNUM, POSITIONDESC ORDER BY STARTDATE) AS Rate_Column,
           'Change_date' || ROW_NUMBER() OVER (PARTITION BY CONTRACTNUM, POSITIONDESC ORDER BY STARTDATE) AS Change_Date_Column
    FROM BI_HZ_ETL.LEM_CNRLEMCRAFTRATE
    WHERE CONTRACTNUM = 'A689'
)
PIVOT (
    MAX(RATE801) AS Rate, MAX(STARTDATE) AS Change_Date
    FOR Rate_Column IN ('Rate1' AS Rate1, 'Rate2' AS Rate2, 'Rate3' AS Rate3, 'Rate4' AS Rate4, 'Rate5' AS Rate5),
    FOR Change_Date_Column IN ('Change_date1' AS Change_date1, 'Change_date2' AS Change_date2, 'Change_date3' AS Change_date3, 'Change_date4' AS Change_date4, 'Change_date5' AS Change_date5)
)
ORDER BY CONTRACTNUM, POSITIONDESC;

This query will pivot the data based on the POSITIONDESC column, creating separate columns for rates and change dates as per the desired output format. Adjust the column names and values as needed to match your specific data structure and requirements.

To transform your data from the current format to the desired format, you can use a pivot operation. However, SQL does not directly support dynamic pivot operations. You would need to know the number of rates and change dates beforehand or use a procedural language like PL/SQL (in Oracle) or T-SQL (in SQL Server) to dynamically generate the pivot query.

Here is an example of how you might do it in SQL Server:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.RATE801) 
            FROM table_name c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT CONTRACTNUM, POSITIONDESC, ' + @cols + ' from 
            (
                select CONTRACTNUM
                    , POSITIONDESC
                    , RATE801
                    , STARTDATE
                from table_name
           ) x
            pivot 
            (
                 max(STARTDATE)
                for RATE801 in (' + @cols + ')
            ) p '

execute(@query)

Please replace table_name with your actual table name. This script dynamically generates the pivot query to handle any number of rates. However, this is just a basic example. Depending on your database and requirements, you might need to adjust this script.

Thanks

try this

SELECT *
FROM (
    SELECT 
        CONTRACTNUM,
        POSITIONDESC,
        RATE801,
        STARTDATE,
        'Rate' || ROW_NUMBER() OVER (PARTITION BY CONTRACTNUM, POSITIONDESC ORDER BY STARTDATE) AS Rate_Column,
        'Change_date' || ROW_NUMBER() OVER (PARTITION BY CONTRACTNUM, POSITIONDESC ORDER BY STARTDATE) AS Change_date_Column
    FROM 
        BI_HZ_ETL.LEM_CNRLEMCRAFTRATE
    WHERE 
        CONTRACTNUM = 'A689'
)
PIVOT (
    MAX(RATE801) AS Rate,
    MAX(STARTDATE) AS Change_date
    FOR Rate_Column IN ('Rate1' AS Rate1, 'Rate2' AS Rate2, 'Rate3' AS Rate3, 'Rate4' AS Rate4, 'Rate5' AS Rate5)
)
ORDER BY 
    CONTRACTNUM,
    POSITIONDESC;