Solved

flatten the table

Posted on 2016-07-28
6
52 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 74

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 74

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 74

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

808 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