Split row into multiple rows Oracle

Question

I have a table with 5 columns: ID, ERROR1, ERROR2, ERROR3, ERROR4.

A small sample would look like:

ID | Error 1 | Error 2 | Error 3 | Error 4 |
12 | YES     | (null)  |  (null) | YES |
15 |  (null) | YES     |  (null) | YES |

So, I need to understand how to break a single row of data where there are multiple columns with "Yes" and turn it into multiple instances of the same ID, and only a single column reading Yes for that instance. So two records of 12 and two records of 15, each having only one error and the rest Null for any individual row.

Thank you

DirkNM · Accepted Answer · 2014-10-13 17:00:21Z

3

Maybe that help, but I'm not sure, if I understand your expected result correctly:

 SELECT ID, Error1, NULL AS Error2, NULL AS Error3, NULL AS Error4
   FROM table
  WHERE Error1 = 'YES'
  UNION
    ALL
 SELECT ID, NULL AS Error1, Error2, NULL AS Error3, NULL AS Error4
   FROM table
  WHERE Error2 = 'YES'
  UNION
    ALL
 SELECT ID, NULL AS Error1, NULL AS Error2, Error3, NULL AS Error4
   FROM table
  WHERE Error3 = 'YES'
   UNION
    ALL
 SELECT ID, NULL AS Error1, NULL AS Error2, NULL AS Error3, Error4
   FROM table
  WHERE Error4 = 'YES'

answered Oct 13, 2014 at 17:00

DirkNM

2,66417 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sylvain Leroux · Accepted Answer · 2014-10-13 18:08:51Z

0

As an alternative solution, you could join your table on a "diagonal matrix", taking benefice in the join clause that NULL is not equal to NULL:

SELECT T.ID, O.*
FROM T JOIN (
 --
 -- build a diagonal matrix
 --
 SELECT 'YES' as "Error 1", NULL as "Error 2", NULL as "Error 3", NULL as "Error 4"
  FROM DUAL
 UNION ALL SELECT NULL, 'YES', NULL, NULL
  FROM DUAL
 UNION ALL SELECT NULL, NULL, 'YES', NULL
  FROM DUAL
 UNION ALL SELECT NULL, NULL, NULL, 'YES'
  FROM DUAL
) O
ON T."Error 1" = O."Error 1"
OR T."Error 2" = O."Error 2"
OR T."Error 3" = O."Error 3"
OR T."Error 4" = O."Error 4";

See http://sqlfiddle.com/#!4/bc0a7f/12

edited Oct 13, 2014 at 18:08

answered Oct 13, 2014 at 18:02

Sylvain Leroux

52.3k8 gold badges114 silver badges136 bronze badges

Comments

Neerav Kumar · Accepted Answer · 2014-10-14 01:01:05Z

0

I guess you are looking for the output for the following query

SELECT ID,
DECODE(COL_NUM,
    1, "ERROR 1",
    2, "ERROR 2",
    3, "ERROR 3",
    "ERROR 4") AS ERROR_COL
DECODE(COL_NUM,
        1, ERROR1,
        2, ERROR2,
        3, ERROR3,
        ERROR4) AS ERROR
FROM
TABLE,
(SELECT ROWNUM AS COL_NUM FROM DUAL WHERE ROWNUM<5)
WHERE
DECODE(COL_NUM,
        1, ERROR1,
        2, ERROR2,
        3, ERROR3,
        ERROR4) IS NOT NULL

ID----ERROR_COL----ERROR

12----ERROR 1------YES

12----ERROR 4------YES

15----ERROR 2------YES

15----ERROR 4------YES

answered Oct 14, 2014 at 1:01

Neerav Kumar

845 bronze badges

Collectives™ on Stack Overflow

Split row into multiple rows Oracle

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related