Pivot Table in Oracle

Question

Hi My data is like this formate.-Using Query SELECT DISTINCT W.CONTRACTNUM W.STARTDATEW.POSITIONDESC W.RATE801 FROM BI_HZ_ETL.LEM_CNRLEMCRAFTRATE W WHERE W.CONTRACTNUM='A689' GROUP BY W.CONTRACTNUMW.POSITIONDESCW.STARTDATEW.RATE801 ORDER BY W.CONTRACTNUMW.POSITIONDESCW.STARTDATE CONTRACTNUM POSITIONDESC RATE801 STARTDATE A689 ACC+PR 71.88 2/6/2022 A689 ACC+PR 72.77 1/1/2023 A689 ACC+PR 73 1/1/2024 A689 ADM+L1 45.07 2/6/2022 A689 ADM+L1 47.86 1/1/2023 A689 ADM+L1 48.01 1/1/2024 A689 ADM+TK 53.11 2/6/2022 A689 ADM+TK 56.41 1/1/2023 A689 ADM+TK 56.58 1/1/2024 A689 CO+HSE/DSP 93.15 1/1/2023 A689 CO+HSE/DSP 93.15 1/1/2024 A689 CO+MAT 67.35 2/6/2022 A689 CO+MAT 72.23 1/1/2023 A689 CO+MAT 72.46 1/1/2024 A689 CO+PR 71.88 2/6/2022 A689 CO+PR 76.37 1/1/2023 A689 CO+PR 76.61 1/1/2024 A689 DRV+PRIN 70.74 11/6/2022 A689 DRV+PRIN 71.82 1/1/2023 A689 DRV+PRIN 73.4 3/5/2023 A689 DRV+PRIN 77.12 11/5/2023 A689 DRV+PRIN 77.33 1/1/2024 A689 DRV+PRIN/CST/TB1 73.99 11/6/2022 A689 DRV+PRIN/CST/TB1 75.11 1/1/2023 A689 DRV+PRIN/CST/TB1 76.69 3/5/2023 A689 DRV+PRIN/CST/TB1 80.38 11/5/2023 A689 DRV+PRIN/CST/TB1 80.61 1/1/2024 I would like to have data output like below CONTRACTNUM POSITIONDESC Rate1 Change_date1 Rate2 Change_date2 Rate3 Change_date3 Rate4 Change-date4 Rate5 Change_date5 A689 ACC+PR 71.88 2/6/2022 72.77 1/1/2023 A689 ADM+L1 45.07 2/6/2022 47.86 1/1/2023 47.01 1/1/2024 A689 ADM+TK 53.11 2/6/2022 56.41 1/1/2023 55.58 1/1/2024 A689 CO+HSE/DSP 93.15 1/1/2023 93.15 1/1/2024 A689 CO+MAT 67.35 2/6/2022 72.23 1/1/2023 71.46 1/1/2024 A689 CO+PR 71.88 2/6/2022 76.37 1/1/2023 75.61 1/1/2024 A689 DRV+PRIN 70.74 11/6/2022 71.82 1/1/2023 73.4 3/5/2023 77.12 11/5/2023 77.33 1/1/2024 A689 DRV+PRIN/CST/TB1 73.99 11/6/2022 75.11 1/1/2023 76.69 3/5/2023 80.38 11/5/2023 80.61 1/1/2024

Naimish Makwana · Answer

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)') 11'') 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

Jayraj Chhaya · Answer

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.

Prasad Raveendran · Answer

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;

Prasad Raveendran · Answer

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;

Rupal Patel · Answer

Good Morning Prasad Raveendran This is the error I got Thanks lots for your time and effort

Prasad Raveendran · Answer

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.

Rupal Patel · Answer

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

Rupal Patel · Answer

Hello Jayraj Thanks for your time I am getting error Please

Rupal Patel · Answer

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 please help me here Thanks

CONTRACTNUM	POSITIONDESC	RATE801	STARTDATE
A689	ACC+PR	71.88	2/6/2022
A689	ACC+PR	72.77	1/1/2023
A689	ACC+PR	73	1/1/2024
A689	ADM+L1	45.07	2/6/2022
A689	ADM+L1	47.86	1/1/2023
A689	ADM+L1	48.01	1/1/2024
A689	ADM+TK	53.11	2/6/2022
A689	ADM+TK	56.41	1/1/2023
A689	ADM+TK	56.58	1/1/2024
A689	CO+HSE/DSP	93.15	1/1/2023
A689	CO+HSE/DSP	93.15	1/1/2024
A689	CO+MAT	67.35	2/6/2022
A689	CO+MAT	72.23	1/1/2023
A689	CO+MAT	72.46	1/1/2024
A689	CO+PR	71.88	2/6/2022
A689	CO+PR	76.37	1/1/2023
A689	CO+PR	76.61	1/1/2024
A689	DRV+PRIN	70.74	11/6/2022
A689	DRV+PRIN	71.82	1/1/2023
A689	DRV+PRIN	73.4	3/5/2023
A689	DRV+PRIN	77.12	11/5/2023
A689	DRV+PRIN	77.33	1/1/2024
A689	DRV+PRIN/CST/TB1	73.99	11/6/2022
A689	DRV+PRIN/CST/TB1	75.11	1/1/2023
A689	DRV+PRIN/CST/TB1	76.69	3/5/2023
A689	DRV+PRIN/CST/TB1	80.38	11/5/2023
A689	DRV+PRIN/CST/TB1	80.61	1/1/2024

CONTRACTNUM	POSITIONDESC	Rate1	Change_date1	Rate2	Change_date2	Rate3	Change_date3	Rate4	Change-date4	Rate5	Change_date5
A689	ACC+PR	71.88	2/6/2022	72.77	1/1/2023
A689	ADM+L1	45.07	2/6/2022	47.86	1/1/2023	47.01	1/1/2024
A689	ADM+TK	53.11	2/6/2022	56.41	1/1/2023	55.58	1/1/2024
A689	CO+HSE/DSP	93.15	1/1/2023	93.15	1/1/2024
A689	CO+MAT	67.35	2/6/2022	72.23	1/1/2023	71.46	1/1/2024
A689	CO+PR	71.88	2/6/2022	76.37	1/1/2023	75.61	1/1/2024
A689	DRV+PRIN	70.74	11/6/2022	71.82	1/1/2023	73.4	3/5/2023	77.12	11/5/2023	77.33	1/1/2024
A689	DRV+PRIN/CST/TB1	73.99	11/6/2022	75.11	1/1/2023	76.69	3/5/2023	80.38	11/5/2023	80.61	1/1/2024