?
Solved

flatten the table

Posted on 2016-07-28
6
Medium Priority
?
66 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
6 Comments
 
LVL 5

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 1600 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 49

Assisted Solution

by:PortletPaul
PortletPaul earned 400 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 1600 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 1600 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup
Suggested Courses

770 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