Using pivot on multiple columns

Question

I have the following sample data in an Oracle table (tab1) and I am trying to convert rows to columns. With pivot table I am able to have person_id like intended but in columns I can only achieve to have every job_id in different column and salary in rows. Is it possible to get it in form I need?

Sample data:

person_id	job_id	salary
1	50	100
1	51	125
2	51	115
2	54	105
2	55	80

My intended output:

person_id	job_1	salary_1	job_2	salary_2	job_3	salary_3
1	50	100	51	125	null	null
2	51	115	54	105	55	80

I tried to use pivot somehow as here I found similar problems: https://stewashton.wordpress.com/2018/05/28/generic-pivot-function/

Using pivot on multiple columns of an Oracle row

As It is not exaclty the same I'm stuck trying by trial and error as well as searching google for simillar question but I cannot find any.

SQL Pivot on multiple columns with INT and NVARCHAR datatypes - best answer here could help me but I can't have every job_id in different column as there is around 1000 different jobs and every person has 2-5 jobs. It means that I can have 5*2+1 columns at max.

I am using oracle 19 if it matters.

MT0 · Accepted Answer · 2023-09-18 13:06:14Z

1

You can use the ROW_NUMBER analytic function to number the jobs for each person and then PIVOT based on those numbers:

SELECT *
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY job_id) AS rn
  FROM   table_name t
)
PIVOT (
  MAX(job_id) AS job_id,
  MAX(salary) AS salary
  FOR rn IN (1 AS J1, 2 AS J2, 3 AS J3)
)

Which, for the sample data:

CREATE TABLE table_name (person_id, job_id, salary) AS
SELECT 1, 50, 100 FROM DUAL UNION ALL
SELECT 1, 51, 125 FROM DUAL UNION ALL
SELECT 2, 51, 115 FROM DUAL UNION ALL
SELECT 2, 54, 105 FROM DUAL UNION ALL
SELECT 2, 55,  80 FROM DUAL;

Outputs:

PERSON_ID	J1_JOB_ID	J1_SALARY	J2_JOB_ID	J2_SALARY	J3_JOB_ID	J3_SALARY
1	50	100	51	125	null	null
2	51	115	54	105	55	80

Note: If you want to rename the columns then use aliases such as SELECT person_id, j1_job_id AS job_1, ....

fiddle

answered Sep 18, 2023 at 13:06

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ORA20001 Over a year ago

Wow, that was quick. Thank you very much, that's exaclty what I need. However would it be possible to have dynamic number of columns? For example if I write clause 'WHERE t.person_id = 1' after FROM table_name t I would get in return just nulls in J3 columns. Can i get rid of them somehow? dbfiddle.uk/3cIN4ueH

MT0 Over a year ago

@ORA20001 SQL (not just Oracle's dialect) requires a fixed number of columns in a query. You could use dynamic SQL to work out the maximum columns you need for a query and then only generate that query but it's easier just to generate all 5 pairs of columns and then ignore the extra ones if you don't need it in whatever third-party program you are using to access the database (i.e. Java, Python, C#, PHP, etc.).

MT0 Over a year ago

@SlavaMurygin Of course it doesn't. The question is tagged Oracle and not SQL Server.

MT0 Over a year ago

@SlavaMurygin If you want to ask how to do it using SQL Server, then ask a new question and tag the question with SQL Server. I don't know what you expect to occur commenting on an Oracle question and asking why it doesn't work in a different language - the answer is that it doesn't work because they are different RDBMS and use different syntaxes.

MT0 Over a year ago

@SlavaMurygin Or search StackOverflow for [sql-server] pivot multiple columns which leads you to this answer.

Collectives™ on Stack Overflow

Using pivot on multiple columns

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related