flatten the table

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
sam2929Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
sdstuberConnect With a Mentor Commented:
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
 
Abhimanyu SuriSr Database EngineerCommented:
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
 
sdstuberConnect With a Mentor Commented:
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
 
PaulConnect With a Mentor Commented:
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
 
sdstuberConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.