Pivot/unpivot

Dorian Grim · Mar 6, 2019 10:09AM

I am on Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

======

I need:

create table my_table_name  (  a varchar2(255)  ,b varchar2(255)  ,c varchar2(255)  ,jan int  ,feb int  ,mar int  ,apr int);insert into (a, b, c, jan, feb, mar, apr) values ('a', 'b', 'c', 11, 1, 12, 111);

a	b	c	jan	feb	mar	apr
a	b	c	11	1	12	111

to look like:

a	b	c	date_id	val
a	b	c	jan	11
a	b	c	feb	1
a	b	c	mar	12
a	b	c	apr	111

Could someone please help me achieve this?

I know of UNION, but I don't want to use it in hopes there's something already there for me, built-in.

with main as(  select     'jan' date_id    ,a    ,b    ,c    ,jan as val  from     my_table_nameunion all  select     'feb' date_id    ,a    ,b    ,c    ,feb as val  from     my_table_nameunion all  select     'mar' date_id    ,a    ,b    ,c    ,mar as val  from     my_table_nameunion all  select     'apr' date_id    ,a    ,b    ,c    ,apr as val  from     my_table_name)select * from main;

Thanks,

Dorian

Frank Kulash · Mar 6, 2019 10:42AM

Hi,

3508532 wrote:Thanks for the reply. Sorry for the unconventional postI felt they can, but I can't get my head around the logic.

Here's one way to use SELECT ... UNPIVOT:

SELECT    a, b, c, date_id, valFROM      mainUNPIVOT  (    val         FOR  date_id  IN (jan, feb, mar, apr)         );

Frank Kulash · Mar 6, 2019 10:26AM

Hi, Dorian,

Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.
It always helps to show what you've tried, that is, the code that seems to come closest to what you want.

Always say which version of Oracle you're using (for example, 12.2.0.1.0).
See the Forum FAQ:

3508532 wrote:I need:abcjanfebmaraprabc11112111to look like:abcdate_idvalabcjan11abcfeb1abcmar12abcapr111Could someone please help me achieve this?Thanks,Dorian

SELECT ... UNPIVOT can do that.

I'm assuming jan, feb, mar, ... in your original table are all the same data type. If not, you'll have to convert some of them before unpivoting.

Dorian Grim · Mar 6, 2019 10:30AM

Thanks for the reply. Sorry for the unconventional post

I felt they can, but I can't get my head around the logic.

BluShadow · Mar 6, 2019 10:30AM

You mean like this?

SQL> ed
Wrote file afiedt.buf  1  with main as (
  2    select 'a' as a, 'b' as b, 'c' as c, 11 as jan, 1 as feb, 12 as mar, 111 as apr from dual
  3    )
  4  -- end of test data
  5  select a,b,c
  6        ,case level
  7          when 1 then 'jan'
  8          when 2 then 'feb'
  9          when 3 then 'mar'
 10          when 4 then 'apr'
 11        else null end as date_id
 12        ,case level
 13          when 1 then jan
 14          when 2 then feb
 15          when 3 then mar
 16          when 4 then apr
 17        else null end as val
 18  from main
 19* connect by level <= 4
SQL> /A B C DAT        VAL
- - - --- ----------
a b c jan         11
a b c feb          1
a b c mar         12
a b c apr        111

Solomon Yakobson · Mar 6, 2019 10:37AM

Use row multiplier:

WITH MULTIPLIER AS (

SELECT LEVEL N

FROM DUAL

CONNECT BY LEVEL <= 4

)

SELECT A,

B,

C,

CASE N

WHEN 1 THEN 'jan'

WHEN 2 THEN 'feb'

WHEN 3 THEN 'mar'

WHEN 4 THEN 'apr'

END DATE_ID,

CASE N

WHEN 1 THEN JAN

WHEN 2 THEN FEB

WHEN 3 THEN MAR

WHEN 4 THEN APR

END VAL

FROM YOUR_TABLE,

MULTIPLIER

/

SY.

Solomon Yakobson · Mar 6, 2019 10:39AM

Now try it on multi-row table .

SY.

Frank Kulash · Mar 6, 2019 10:42AM

Hi,

3508532 wrote:Thanks for the reply. Sorry for the unconventional postI felt they can, but I can't get my head around the logic.

Here's one way to use SELECT ... UNPIVOT:

SELECT    a, b, c, date_id, valFROM      mainUNPIVOT  (    val         FOR  date_id  IN (jan, feb, mar, apr)         );

mathguy · Mar 6, 2019 10:52AM

Something like this. Note that if you must order by DATE_ID, you must use TO_DATE around it (I show one simple, perhaps unrealistic, example in the code below). Of course, you could instead use TO_DATE in the SELECT list (making the DATE_ID into a DATE column, rather than VARCHAR2) - then no further magic would be needed in ORDER BY.

with  my_table_name (a, b, c, jan, feb, mar, apr) as (    select 'a', 'b', 'c',  11,  1,  12, 111 from dual union all    select 'A', 'x', 'z',  3,  8, 200,  15 from dual  )-- end of sample data; you don't need the WITH clause, use your actual table and column names below.select   a, b, c, date_id, valfrom     my_table_nameunpivot  (val for date_id in (jan as 'jan', feb as 'feb', mar as 'mar', apr as 'apr'))order by to_date(date_id, 'mon'), a, b, c  -- or whatever you need;A B C DATE_ID        VAL- - - ------- ----------A x z jan              3a b c jan             11A x z feb              8a b c feb              1A x z mar            200a b c mar             12A x z apr             15a b c apr            111

mathguy · Mar 6, 2019 11:05AM

Regarding the MULTIPLIER (helper) view, of course the way you wrote it is correct, but perhaps the following variation is more intuitive.

with  multiplier as (select column_value as date_id from sys.odcivarchar2list('jan', 'feb', 'mar', 'apr'))....

- then the main query code can also be simplified. Earlier Oracle versions may need the TABLE( ) operator, but the idea is the same.

Or - assuming, for example, that in the end DATE_ID will have to be of DATE data type, that can also be done in the MULTIPLIER view.

BluShadow · Mar 6, 2019 11:36AM

Solomon Yakobson wrote:Now try it on multi-row table .SY.

C'mon Solomon you know the OP didn't ask for a multi-row table, he just asked for a single row to be unpivoted.

Of course, you know I can do that...

SQL> ed
Wrote file afiedt.buf  1  with main as (
  2      select 'a' as a, 'b' as b, 'c' as c, 11 as jan, 1 as feb, 12 as mar, 111 as apr from dual union all
  3      select 'd' as a, 'e' as b, 'f' as c, 22 as jan, 2 as feb, 24 as mar, 222 as apr from dual
  4      )
  5  -- end of test data
  6  select a,b,c
  7        ,case level
  8          when 1 then 'jan'
  9          when 2 then 'feb'
 10          when 3 then 'mar'
 11          when 4 then 'apr'
 12        else null end as date_id
 13        ,case level
 14          when 1 then jan
 15          when 2 then feb
 16          when 3 then mar
 17          when 4 then apr
 18        else null end as val
 19  from main
 20  connect by level <= 4
 21  and prior a = a
 22  and prior b = b
 23  and prior c = c
 24* and prior sys_guid() is not null
SQL> /A B C DAT        VAL
- - - --- ----------
a b c jan         11
a b c feb          1
a b c mar         12
a b c apr        111
d e f jan         22
d e f feb          2
d e f mar         24
d e f apr        2228 rows selected.

LOL!

mathguy · Mar 6, 2019 11:49AM

Here you are assuming that the composite (a, b, c) (three columns) is unique, in the data if not by design. There is nothing in the OP's question to suggest that that's the case. You either need a PK, or you can use ROWID - or, best, use a cross join, as used to be done in the old days; no need for CONNECT BY in the main query, it can be used just to create the MULTIPLIER view in SY's answer. Much simpler and more efficient, and there are no complications.

Forum Stats

Quick Links

Categories

Pivot/unpivot

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Pivot/unpivot

Best Answer

Answers