OraFAQ Forum: SQL & PL/SQL » Transposing data in single row

Home » SQL & PL/SQL » SQL & PL/SQL » Transposing data in single row (Oracle 12 c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Transposing data in single row [message #680453]

Fri, 15 May 2020 06:30

OracleDBA_beginner
Messages: 5
Registered: July 2013

Junior Member

Hello,

I have data in the following format in a table. Each column has data only once for a particular ID.

ID Col1 Col2 Col3 COl4 Col5
63467855 NULL 345768 NULL NULL NULL
63467855 NULL NULL 903923 NULL NULL
63467855 NULL NULL NULL NULL 9028345
63467855 NULL NULL NULL 739034 NULL
63467855 3624075 NULL NULL NULL NULL
67567474 NULL 467583 NULL NULL NULL
67567474 9867488 NULL NULL NULL NULL
67567474 NULL NULL 8473874 NULL NULL
67567474 NULL NULL NULL NULL 7583739
67567474 NULL NULL NULL 7283764 NULL
47876322 5773948 NULL NULL NULL NULL
47876322 NULL NULL 1238967 NULL NULL
47876322 NULL 4758690 NULL NULL NULL
47876322 NULL NULL NULL 8907612 NULL
47876322 NULL NULL NULL NULL 4578232

I need to convert it into one row for each ID while getting rid of NULLs.

So the output need to be like this -

ID Col1 Col2 Col3 COl4 Col5
63467855 3624075 345768 903923 739034 9028345
67567474 9867488 467583 8473874 7283764 7583739
47876322 5773948 4758690 1238967 8907612 4578232

Can you please advise with SQL query that I should write to have above output.

Thanks a lot in advance!

Report message to a moderator

Re: Transposing data in single row [message #680454 is a reply to message #680453]

Fri, 15 May 2020 06:53

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

Plain group by.

SY.

Report message to a moderator

Re: Transposing data in single row [message #680456 is a reply to message #680453]

Fri, 15 May 2020 07:37

Michel Cadot
Messages: 68775
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Report message to a moderator

Re: Transposing data in single row [message #680459 is a reply to message #680453]

Fri, 15 May 2020 07:52

OracleDBA_beginner
Messages: 5
Registered: July 2013

Junior Member

Hello,

I tried doing max operation on all columns with group by.
Problem is that the table has appx 2500 columns on which I have to put max operator.
Is there any other solution except max(col1), max(col2)... max(col2500)
Group by ID

??

Report message to a moderator

Re: Transposing data in single row [message #680460 is a reply to message #680459]

Fri, 15 May 2020 07:58

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

OracleDBA_beginner wrote on Fri, 15 May 2020 05:52

Hello,

I tried doing max operation on all columns with group by.
Problem is that the table has appx 2500 columns on which I have to put max operator.
Is there any other solution except max(col1), max(col2)... max(col2500)
Group by ID

??

Consider two separate points.
1) I seriously doubt that a table with approx. 2500 columns conforms to Third Normal Form.
2) There is likely no single row where every column is at MAXIMUM value CONCURRENTLY

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Report message to a moderator

Re: Transposing data in single row [message #680463 is a reply to message #680459]

Fri, 15 May 2020 08:10

Michel Cadot
Messages: 68775
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Problem is that the table has appx 2500 columns on which I have to put max operator.

And then?

Report message to a moderator

Re: Transposing data in single row [message #680468 is a reply to message #680463]

Fri, 15 May 2020 08:52

OracleDBA_beginner
Messages: 5
Registered: July 2013

Junior Member

Michel Cadot wrote on Fri, 15 May 2020 18:40

Quote:
Problem is that the table has appx 2500 columns on which I have to put max operator.
And then?

Will it lead to any performance issue because of too many max operators in single query ?

Report message to a moderator

Re: Transposing data in single row [message #680469 is a reply to message #680468]

Fri, 15 May 2020 09:09

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

OracleDBA_beginner wrote on Fri, 15 May 2020 06:52

Michel Cadot wrote on Fri, 15 May 2020 18:40

Quote:
Problem is that the table has appx 2500 columns on which I have to put max operator.
And then?

Will it lead to any performance issue because of too many max operators in single query ?

What do you MEASURE when you run one SQL using MAX & similar SQL without MAX regarding elapsed time?

Report message to a moderator

Re: Transposing data in single row [message #680473 is a reply to message #680468]

Fri, 15 May 2020 11:10

Michel Cadot
Messages: 68775
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

OracleDBA_beginner wrote on Fri, 15 May 2020 15:52

Michel Cadot wrote on Fri, 15 May 2020 18:40

Quote:
Problem is that the table has appx 2500 columns on which I have to put max operator.
And then?

Will it lead to any performance issue because of too many max operators in single query ?

As you have no better (or even other but the worst) choice how does it matter?
Anyway, the answer is no, nothing noticeable more than the full scan you actually need (although with 2500 columns you more likely have chained rows which will be the greatest part of the cost, hope you have 32K blocks).

Report message to a moderator

Re: Transposing data in single row [message #680474 is a reply to message #680459]

Fri, 15 May 2020 11:10

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

OracleDBA_beginner wrote on Fri, 15 May 2020 08:52

Problem is that the table has appx 2500 columns

??

Really, could you share the "magic"? Last time I checked column limit still was 1000 columns per table. Anyway, you can create a view and reference it where you need to grouped data. And you can generate create view statement from user_tab_columns data dictionary view.

SY.

Report message to a moderator