Oracle SQL - Transposing columns as rows while keeping one column

Question

I have the following table (it is really a view that is derived from complicate logic):

|    TYPE | FIELD1 | FIELD2 | FIELD3 | FIELDNEEDSTOSTAYASCOLUMN |
|---------|--------|--------|--------|--------------------------|
|     bad |      1 |      1 |      1 |                     1000 |
| neutral |      2 |      2 |      2 |                     1000 |
|    good |      3 |      3 |      3 |                     1000 |

I need to pivot it to the below. Take all the columns and move them as thr rows, but I need to keep the last column.

|   FIELD | BAD    | NEUTRAL| GOOD   | FIELDNEEDSTOSTAYASCOLUMN |
|---------|--------|--------|--------|--------------------------|
| FIELD1  |      1 |      1 |      1 |                     1000 |
| FIELD2  |      2 |      2 |      2 |                     1000 |
| FIELD3  |      3 |      3 |      3 |                     1000 |

I have tried using ORacl'es PIVOT and UNPIVOT but have been unable to create this.

This is a fiddle of the sql:

http://sqlfiddle.com/#!4/8fb63/10

user5683823 · Accepted Answer · 2019-10-10 18:40:37Z

2

select  field, bad, neutral, good, fieldneedstostayascolumn
from    mytable
unpivot (val for (field, ord) in (field1 as ('FIELD1', 1), field2 as ('FIELD2', 2),
                                  field3 as ('FIELD3', 3)))
pivot   (min(val) for type in ('bad' as bad, 'neutral' as neutral, 'good' as good))
order   by ord
;



FIELD   BAD NEUTRAL GOOD FIELDNEEDSTOSTAYASCOLUMN
------ ---- ------- ---- ------------------------
FIELD1    1       2    3                     1000
FIELD2    1       2    3                     1000
FIELD3    1       2    3                     1000

answered Oct 10, 2019 at 18:40

user5683823

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

Comments

dotnetboss · Accepted Answer · 2019-10-10 18:39:00Z

2

Here you go

WITH T1 AS (SELECT
   PROPERTY AS FIELD,
   TYPE,

   VALUE as VAL,
            FIELDNEEDSTOSTAYASCOLUMN
FROM
   MYTABLE
UNPIVOT
EXCLUDE NULLS
(
   VALUE
   FOR
      PROPERTY
   IN
   (
      FIELD1,
      FIELD2,
     FIELD3
   )
))
SELECT * FROM T1
PIVOT
(
   max(VAL)
   FOR
      (TYPE)
   IN
      ('bad','neutral','good')
)
order by FIELD;

By the way, your results are not right.

http://sqlfiddle.com/#!4/8fb63/30/0

answered Oct 10, 2019 at 18:39

dotnetboss

544 bronze badges

Collectives™ on Stack Overflow

Oracle SQL - Transposing columns as rows while keeping one column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related