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.