Home » SQL & PL/SQL » SQL & PL/SQL » Transposing data in single row (Oracle 12 c)
Transposing data in single row [message #680453] Fri, 15 May 2020 06:30 Go to next message
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!
Re: Transposing data in single row [message #680454 is a reply to message #680453] Fri, 15 May 2020 06:53 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2985
Registered: January 2010
Location: Connecticut, USA
Senior Member
Plain group by.

SY.
Re: Transposing data in single row [message #680456 is a reply to message #680453] Fri, 15 May 2020 07:37 Go to previous messageGo to next message
Michel Cadot
Messages: 67367
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Transposing data in single row [message #680459 is a reply to message #680453] Fri, 15 May 2020 07:52 Go to previous messageGo to next message
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

??
Re: Transposing data in single row [message #680460 is a reply to message #680459] Fri, 15 May 2020 07:58 Go to previous messageGo to next message
BlackSwan
Messages: 26733
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
Re: Transposing data in single row [message #680463 is a reply to message #680459] Fri, 15 May 2020 08:10 Go to previous messageGo to next message
Michel Cadot
Messages: 67367
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: Transposing data in single row [message #680468 is a reply to message #680463] Fri, 15 May 2020 08:52 Go to previous messageGo to next message
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 ?
Re: Transposing data in single row [message #680469 is a reply to message #680468] Fri, 15 May 2020 09:09 Go to previous messageGo to next message
BlackSwan
Messages: 26733
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?
Re: Transposing data in single row [message #680473 is a reply to message #680468] Fri, 15 May 2020 11:10 Go to previous messageGo to next message
Michel Cadot
Messages: 67367
Registered: March 2007
Location: Nanterre, France, http://...
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).

Re: Transposing data in single row [message #680474 is a reply to message #680459] Fri, 15 May 2020 11:10 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2985
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.
Re: Transposing data in single row [message #680489 is a reply to message #680474] Sun, 17 May 2020 07:38 Go to previous messageGo to next message
Amine
Messages: 318
Registered: March 2010
Senior Member

is it normal to have a table with 1000 columns ?
Re: Transposing data in single row [message #680492 is a reply to message #680489] Mon, 18 May 2020 04:58 Go to previous message
Lalit Kumar B
Messages: 3171
Registered: May 2013
Location: World Wide on the Web
Senior Member
Normalization

Good explanation by Jonathan on similar topic:

https://jonathanlewis.wordpress.com/2015/02/19/255-columns/
https://jonathanlewis.wordpress.com/2017/05/19/255-columns-2/
https://jonathanlewis.wordpress.com/2018/02/28/255-columns-3/

[Updated on: Mon, 18 May 2020 05:13]

Report message to a moderator

Previous Topic: Procedure creation help
Next Topic: Using SELECT to display various totals
Goto Forum:
  


Current Time: Wed Sep 23 01:52:49 CDT 2020