Solved

flatten the table

Posted on 2016-07-28
6
32 Views
Last Modified: 2016-08-22
Hi,
I have records coming like below i want to flatten them out.

source looks like:

emplid  rcd  disable_cd  disable_desc

123     1      23           ear infection

123     1       42           broken hand

123      1      16           aa


234     2       11          bb

234      2      16          kk


How can i  flatten it based upon emplid rcd


Target should be:


emplid  rcd   disable_cd_1   disable_cd_2   disable_cd_3  disable_cd_4 disable_desc_1 disable_desc_2 disable_desc_3 disable_desc_4

123      1      23              42              16           null          ear infection    broken hand   aa              null

234      2      11              16             null          null                 bb           kk         null            null
0
Comment
Question by:sam2929
  • 3
6 Comments
 
LVL 4

Expert Comment

by:Abhimanyu Suri
ID: 41734057
set lines 200 -- to put o/pin single line limiting to 200 character

col emplid for 999 -- assuming emplid can never be more than 3 digits , set other columns similarly

set pages 200 -- Number of lines in a page

Reference :

http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12040.htm
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 400 total points (awarded by participants)
ID: 41734071
There are no sqlplus settings that will pivot the data for you.

You must do that with code.  For example...


SELECT emplid,
         rcd,
         MAX(CASE WHEN rn = 1 THEN disable_cd END) disable_cd_1,
         MAX(CASE WHEN rn = 2 THEN disable_cd END) disable_cd_2,
         MAX(CASE WHEN rn = 3 THEN disable_cd END) disable_cd_3,
         MAX(CASE WHEN rn = 4 THEN disable_cd END) disable_cd_4,
         MAX(CASE WHEN rn = 1 THEN disable_desc END) disable_desc_1,
         MAX(CASE WHEN rn = 2 THEN disable_desc END) disable_desc_2,
         MAX(CASE WHEN rn = 3 THEN disable_desc END) disable_desc_3,
         MAX(CASE WHEN rn = 4 THEN disable_desc END) disable_desc_4
    FROM (SELECT t.*, ROW_NUMBER() OVER(PARTITION BY emplid, rcd ORDER BY 1) rn
            FROM yourtable t)
GROUP BY emplid, rcd
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 100 total points (awarded by participants)
ID: 41734073
use row_number() over() to assign numbers 1 to 4 (and beyond)
then use case expressions inside max() to push the data into columns
select
      emplid
    , rcd
    , max(case when rn = 1 then disable_cd end) as disable_cd_1
    , max(case when rn = 2 then disable_cd end) as disable_cd_2
    , max(case when rn = 3 then disable_cd end) as disable_cd_3
    , max(case when rn = 4 then disable_cd end) as disable_cd_4
    , max(case when rn = 1 then disable_desc end) as disable_desc_1
    , max(case when rn = 2 then disable_desc end) as disable_desc_2
    , max(case when rn = 3 then disable_desc end) as disable_desc_3
    , max(case when rn = 4 then disable_desc end) as disable_desc_4
from (
        select
              emplid, rcd, disable_cd, disable_desc
            , row_number() over(partition by emplid order by rcd, disable_cd) as rn
        from your_table
     )
where rn < 5
group by
      emplid
    , rcd
;

Open in new window

0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 400 total points (awarded by participants)
ID: 41734079
or use built in PIVOT syntax

SELECT emplid,
       rcd,
       "1_CD"  AS disable_cd_1,
       "2_CD"  AS disable_cd_2,
       "3_CD"  AS disable_cd_3,
       "4_CD"  AS disable_cd_4,
       "1_DSC" AS disable_dsc_1,
       "2_DSC" AS disable_dsc_2,
       "3_DSC" AS disable_dsc_3,
       "4_DSC" AS disable_dsc_4
  FROM (SELECT t.*, ROW_NUMBER() OVER(PARTITION BY emplid, rcd ORDER BY 1) rn
          FROM yourtable t)
       PIVOT
           (MAX(disable_cd) AS cd, MAX(disable_desc) AS dsc
           FOR rn
           IN (1, 2, 3, 4))
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 400 total points (awarded by participants)
ID: 41734083
another version, same idea as previous post, but I prefer the aliasing this way

SELECT emplid,
       rcd,
       a_cd  AS disable_cd_1,
       b_cd  AS disable_cd_2,
       c_cd  AS disable_cd_3,
       d_cd  AS disable_cd_4,
       a_dsc AS disable_dsc_1,
       b_dsc AS disable_dsc_2,
       c_dsc AS disable_dsc_3,
       d_dsc AS disable_dsc_4
  FROM (SELECT t.*, ROW_NUMBER() OVER(PARTITION BY emplid, rcd ORDER BY 1) rn
          FROM yourtable t)
       PIVOT
           (MAX(disable_cd) AS cd, MAX(disable_desc) AS dsc
           FOR rn
           IN (1 a, 2 b, 3 c, 4 d))
1

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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.

760 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

23 Experts available now in Live!

Get 1:1 Help Now