Solved

How to turn multiple rows into one row in oracle without creating functions or views

Posted on 2014-03-14
3
594 Views
Last Modified: 2014-03-14
In my database, I have e.g following 2 rows

SSSN                           UNIT           INDV     PROF
330511-000      GYMBER01      INDV          NULL      
330511-000      GYMBER01      NULL          PROF

I want it displayed like this, that is: only one row

SSSN                           UNIT             INDV              PROF
330511-000      GYMBER01        INDV             PROF      




I know how to achieve this by Writing functions or multiple views which I then join, but I would also be good to be able to achieve this if I only have read-rights in the db.

This is the code that returns the first to rows:

select  ssn, UNIT,

case  when kurskod='INDV' then kurskod  end AS INDV,
case  when kurskod='PROF' then kurskod  end AS PROF

from aktivitet


Open in new window

0
Comment
Question by:marcgu
3 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 450 total points
Comment Utility
SELECT sssn,
         unit,
         MAX(CASE WHEN kurskod = 'INDV' THEN kurskod END) indv,
         MAX(CASE WHEN kurskod = 'PROF' THEN kurskod END) prof
    FROM aktivitet
GROUP BY sssn, unit
0
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 50 total points
Comment Utility
The code you posted would not return two rows unless:

   1) The table contain duplicate ssn or
   2) You are either joining the table to itself or doing a UNION.

In any case you could use the MAX() function to eliminate the NULL columns.
0
 

Author Closing Comment

by:marcgu
Comment Utility
Hi!

Great solutions. I give sdstuber most since it was posted first and complete.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now