Skip to Main Content
  • Questions
  • Transformation between ROWs and COLs, then GROUP

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Joe.

Asked: August 16, 2016 - 10:27 am UTC

Last updated: August 26, 2016 - 1:43 pm UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi Team,
Suppose that we have a table named TEST like this:
SQL> SELECT * FROM TEST;

WHO L W H
-------- ---------- ---------- ----------
TOM 10 20 30
TOM 11 21 31
TOM 12 22 32
CHRIS 20 30 40
CHRIS 21 31 41
CHRIS 22 32 42
CONNOR 30 40 50
CONNOR 31 41 51
CONNOR 32 42 52
....
....
....

My customer want to query this table with ONE query statement(rather than PL/SQL), and the result have to be shown like this:

TOM TOM TOM CHRIS CHRIS CHRIS CONNOR CONNOR CONNOR ....
--- --- --- ----- ----- ----- ------ ------ ------ ....
10 20 30 20 30 40 30 40 50 ....
11 21 31 21 31 41 31 41 51 ....
12 22 32 22 32 42 32 42 52 ....


Something about transformation between ROWs and COLs, and 'group by' of course.
I've tried pivot() and listagg(), but failed. That troubles me for several days, what will you guys do? Would you please give me some tips?

and Chris said...

You just need to do the pivot!

You can specify multiple columns that include values you want to appear in your generated columns. Here that's L, W & H:

create table t (
  who varchar2(10),
  l int,
  w int,
  h int
);
insert into t values ('TOM',10,20,30);
insert into t values ('TOM',11,21,31);
insert into t values ('TOM',12,22,32);
insert into t values ('CHRIS',20,30,40);
insert into t values ('CHRIS',21,31,41);
insert into t values ('CHRIS',22,32,42);
insert into t values ('CONNOR',30,40,50);
insert into t values ('CONNOR',31,41,51);
insert into t values ('CONNOR',32,42,52);

select * from t
pivot  (
  min(l) ml, min(w) mw, min(h) mh for who in (
    'TOM' as tom, 'CHRIS' as chris, 'CONNOR' as connor
  )
);

TOM_ML  TOM_MW  TOM_MH  CHRIS_ML  CHRIS_MW  CHRIS_MH  CONNOR_ML  CONNOR_MW  CONNOR_MH  
10      20      30      20        30        40        30         40         50         


But that's not quite right. You want three rows in the results. One from each person in the original.

The min() call has lumped them all together. As you speculate, you want a "group by" to split these out.

You can do this by assigning a number to each row per person. So you get rows 1-3 for Tom, 1-3 for me, etc.

Row_number() fits perfectly here. Place this in an inline view. Then pivot in the outer query:

select * from (
  select row_number() over (partition by who order by l) rn, t.* 
  from   t
)
pivot  (
  min(l) ml, min(w) mw, min(h) mh for who in (
    'TOM' as tom, 'CHRIS' as chris, 'CONNOR' as connor
  )
);

RN  TOM_ML  TOM_MW  TOM_MH  CHRIS_ML  CHRIS_MW  CHRIS_MH  CONNOR_ML  CONNOR_MW  CONNOR_MH  
1   10      20      30      20        30        40        30         40         50         
2   11      21      31      21        31        41        31         41         51         
3   12      22      32      22        32        42        32         42         52         


So what's going on here?

Oracle has transformed the pivot into:

  
select "from$_subquery$_001"."RN" "RN",
  min ( case
    when ( "from$_subquery$_001"."WHO"  ='TOM' ) then "from$_subquery$_001"."L"
  end ) "TOM_ML",
  min ( case 
    when ( "from$_subquery$_001"."WHO"='TOM' ) then "from$_subquery$_001"."W"
  end ) "TOM_MW",
  min ( case
    when ( "from$_subquery$_001"."WHO"='TOM' ) then "from$_subquery$_001"."H"
  end ) "TOM_MH",
  min ( case
    when ( "from$_subquery$_001"."WHO"='CHRIS' ) then "from$_subquery$_001"."L"
  end ) "CHRIS_ML",
  min ( case
    when ( "from$_subquery$_001"."WHO"='CHRIS' ) then "from$_subquery$_001"."W"
  end ) "CHRIS_MW",
  min ( case
    when ( "from$_subquery$_001"."WHO"='CHRIS' ) then "from$_subquery$_001"."H"
  end ) "CHRIS_MH",
  min ( case 
    when ( "from$_subquery$_001"."WHO"='CONNOR' ) then "from$_subquery$_001"."L"
  end ) "CONNOR_ML",
  min ( case
    when ( "from$_subquery$_001"."WHO"='CONNOR' ) then "from$_subquery$_001"."W"
  end ) "CONNOR_MW",
  min ( case 
    when ( "from$_subquery$_001"."WHO"='CONNOR' ) then "from$_subquery$_001"."H"
  end ) "CONNOR_MH"
from
  (select row_number ( ) over ( partition b y "T"."WHO" order by "T"."L" ) "RN",
    "T"."WHO" "WHO","T"."L" "L", "T"."W" "W","T"."H" "H"
  from "CHRIS"."T" "T"
  ) "from$_subquery$_001"
group by " from$_subquery$_001"."RN";


Basically an old-school pivot! Notice the group by at the bottom. This includes all columns in your results which aren't part of the pivot clause.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Implicit grouping, eh?

Duke Ganote, August 16, 2016 - 7:37 pm UTC

I'd wondered about PIVOT before too. But indeed, the documentation says (my bolding added): "The grouping columns and aggregated values ... produce the following cross-tabular output:

"a. All the implicit grouping columns not referred to in the pivot_clause, followed by

"b. New columns corresponding to values in the pivot_in_clause Each aggregated value is transposed to the appropriate new column in the cross-tabulation. If you specify the XML keyword, then the result is a single new column that expresses the data as an XML string."

WITH
t ( ename  , l, w, h ) AS (                  SELECT
   'TOM'   ,10,20,30     FROM DUAL UNION ALL SELECT
   'TOM'   ,11,21,31     FROM DUAL UNION ALL SELECT
   'TOM'   ,12,22,32     FROM DUAL UNION ALL SELECT
   'CHRIS' ,20,30,40     FROM DUAL UNION ALL SELECT
   'CHRIS' ,21,31,41     FROM DUAL UNION ALL SELECT
   'CHRIS' ,22,32,42     FROM DUAL UNION ALL SELECT
   'CONNOR',30,40,50     FROM DUAL UNION ALL SELECT
   'CONNOR',31,41,51     FROM DUAL UNION ALL SELECT
   'CONNOR',32,42,52     FROM DUAL )
,tt AS (
  SELECT row_number() over (partition by ename order by l) r#
       , row_number() over (partition by ename order by l DESC) rr#
       , t.*
    FROM t
)
SELECT *
  FROM tt
pivot  (
  min(l) ml, min(w) mw, min(h) mh for ename in (
    'TOM' as tom, 'CHRIS' as chris, 'CONNOR' as connor
  )
) order by r#;

R#  RR#     TOM_ML     TOM_MW     TOM_MH   CHRIS_ML   CHRIS_MW   CHRIS_MH  CONNOR_ML  CONNOR_MW  CONNOR_MH
-- ---- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
 1    3         10         20         30         20         30         40         30         40      50
 2    2         11         21         31         21         31         41         31         41      51
 3    1         12         22         32         22         32         42         32         42      52


Chris Saxon
August 17, 2016 - 8:30 am UTC

Thanks Duke. The doc link for the curious is:

https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#CHDFAFIE

Sparse matrix style

Duke Ganote, August 17, 2016 - 6:30 pm UTC

Hmm, can see the "original" data in a spare matrix format:

WITH
t ( ename  , l, w, h ) AS (                  SELECT
   'TOM'   ,10,20,30     FROM DUAL UNION ALL SELECT
   'TOM'   ,11,21,31     FROM DUAL UNION ALL SELECT
   'TOM'   ,12,22,32     FROM DUAL UNION ALL SELECT
   'CHRIS' ,20,30,40     FROM DUAL UNION ALL SELECT
   'CHRIS' ,21,31,41     FROM DUAL UNION ALL SELECT
   'CHRIS' ,22,32,42     FROM DUAL UNION ALL SELECT
   'CONNOR',30,40,50     FROM DUAL UNION ALL SELECT
   'CONNOR',31,41,51     FROM DUAL UNION ALL SELECT
   'CONNOR',32,42,52     FROM DUAL )
,tt AS (
  SELECT ename nm
       , row_number() over (partition by ename order by l ) r#
       , t.*
    FROM t
)
SELECT *
  FROM tt
pivot  (
  min(l) ml, min(w) mw, min(h) mh for ename in (
    'TOM' as tom, 'CHRIS' as chris, 'CONNOR' as connor
  )
) order by nm, r#
/

NM             R#     TOM_ML     TOM_MW     TOM_MH   CHRIS_ML   CHRIS_MW   CHRIS_MH  CONNOR_ML  CONNOR_MW  CONNOR_MH
------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
CHRIS           1                                          20         30         40
CHRIS           2                                          21         31         41
CHRIS           3                                          22         32         42
CONNOR          1                                                                           30         40         50
CONNOR          2                                                                           31         41         51
CONNOR          3                                                                           32         42         52
TOM             1         10         20         30
TOM             2         11         21         31
TOM             3         12         22         32


Chris Saxon
August 18, 2016 - 8:01 am UTC

That's because you've got two name columns!

WITH
t ( ename  , l, w, h ) AS (                  SELECT
   'TOM'   ,10,20,30     FROM DUAL UNION ALL SELECT
   'TOM'   ,11,21,31     FROM DUAL UNION ALL SELECT
   'TOM'   ,12,22,32     FROM DUAL UNION ALL SELECT
   'CHRIS' ,20,30,40     FROM DUAL UNION ALL SELECT
   'CHRIS' ,21,31,41     FROM DUAL UNION ALL SELECT
   'CHRIS' ,22,32,42     FROM DUAL UNION ALL SELECT
   'CONNOR',30,40,50     FROM DUAL UNION ALL SELECT
   'CONNOR',31,41,51     FROM DUAL UNION ALL SELECT
   'CONNOR',32,42,52     FROM DUAL )
,tt AS (
  SELECT ename nm
       , row_number() over (partition by ename order by l ) r#
       , t.*
    FROM t
)
select * from tt;

NM      R#  ENAME   L   W   H   
CHRIS   1   CHRIS   20  30  40  
CHRIS   2   CHRIS   21  31  41  
CHRIS   3   CHRIS   22  32  42  
CONNOR  1   CONNOR  30  40  50  
CONNOR  2   CONNOR  31  41  51  
CONNOR  3   CONNOR  32  42  52  
TOM     1   TOM     10  20  30  
TOM     2   TOM     11  21  31  
TOM     3   TOM     12  22  32

NM isn't part of the pivot, so becomes part of the implicit group by...

Thanks

Joe Huang, August 18, 2016 - 9:40 am UTC

Thanks Chris and Duke, that's exactly what i'm looking for :)

problem about ROW-convert-to-COL with DYNAMIC columns

Joe Huang, August 26, 2016 - 11:21 am UTC

Hi, Team:
I was bang into trouble by a ROW-convert-to-COL SQL statement, and I thought I need your help one more time, it's about so called DYNAMIC COLUMNS by my manager.
We have a table named T like this:

create table T
(
  item  VARCHAR2(200),
  val   VARCHAR2(100),
  gid   VARCHAR2(50)
)
;

insert into T values ('SCHEMA_NAME', 'SUC_RATE', '1');
insert into T values ('STD_VAL', '99', '1');
insert into T values ('PRO_LINE', 'A-Line','1');
insert into T values ('ACT_VAL', '98','1');
insert into T values ('SCHEMA_NAME', 'SUC_RATE','2');
insert into T values ('STD_VAL', '99','2');
insert into T values ('PRO_LINE', 'B-Line','2');
insert into T values ('ACT_VAL', '98.2','2');
insert into T values ('FLAG', 'N','1');
insert into T values ('FLAG', 'Y','2');
commit;



SQL> select gid,item,val from t order by item,gid;

GID        ITEM                 VAL
---------- -------------------- ----------
1          ACT_VAL              98
2          ACT_VAL              98.2
1          FLAG                 N
2          FLAG                 Y
1          PRO_LINE             A-Line
2          PRO_LINE             B-Line
1          SCHEMA_NAME          SUC_RATE
2          SCHEMA_NAME          SUC_RATE
1          STD_VAL              99
2          STD_VAL              99


And our customer want a result like this:

GID SCHEMA_NAME STD_VAL ACT_VAL FLAG PRO_LINE 
--- ----------- ------- ------- ---- -------- 
1   SUC_RATE    99      98      N    A-Line   
2   SUC_RATE    99      98.2    Y    B-Line  



I thought it was something about ROW-convert-to-COL stuff, and I've tried to archive this all day long, but failed with my poor SQL skills. Here's my SQL statement:

SQL> select * from (
  select  row_number() over (partition by ITEM order by NULL) rn ,
  t.* 
  from  T t
)
pivot  (
  min(GID) ml, min(VAL) mw for ITEM in (
   'SCHEMA_NAME','STD_VAL','PRO_LINE','ACT_VAL'
  )
);


 RN 'SCHEMA_NAME'_ML 'SCHEMA_NAME'_MW 'STD_VAL'_ML     'STD_VAL'_MW   'PRO_LINE'_ML    'PRO_LINE'_MW    'ACT_VAL'_ML     'ACT_VAL'_MW    
--- ---------------- ---------------- ---------------- -------------- ---------------- ---------------- ---------------- ----------------
  1 1                SUC_RATE         1                99             2                B-Line           1                98
  2 2                SUC_RATE         2                99             1                A-Line           2                98.2


But I could not go one step further, I did not satisfied with our customer's requirments below:
1) the column name should generate from table T(col SCHEMA_NAME), which means it should be *dynamic* (in this case, it means STD_VAL,PRO_LINE,FLAG etc.)
2) the result seems to be wrong, for the value 98.2 should be allocated in 1st row, but it tured out to be 2nd. Something must be wrong with my SQL(in other words, my way of thinking).


Hope you could help us

Chris Saxon
August 26, 2016 - 1:43 pm UTC

It looks to me like you're pivoting by GID. So you don't need to add a row_number()!

Using your test case:

select * from t
pivot  (
  min(VAL) mw for ITEM in (
   'SCHEMA_NAME','STD_VAL','PRO_LINE','ACT_VAL'
  )
);

GID  'SCHEMA_NAME'_MW  'STD_VAL'_MW  'PRO_LINE'_MW  'ACT_VAL'_MW  
1    SUC_RATE          99            A-Line         98            
2    SUC_RATE          99            B-Line         98.2


I'm not sure how you're calculating values for your "FLAG" column though...

If you want dynamic pivot clauses, you have a few options:

- Dynamic SQL
- PIVOT XML (though you get the results in XML...)
- Anton Scheffer's table function:

https://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder

Thanks

Joe Huang, August 29, 2016 - 3:55 am UTC

It worked! Appreciate your timely help :-)

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.