Home » SQL & PL/SQL » SQL & PL/SQL » Transpose rows to columns without aggregation
Transpose rows to columns without aggregation [message #652216] 
Sat, 04 June 2016 09:29 

dancko
Messages: 101 Registered: June 2013 Location: italy

Senior Member 


Hi to all,
Is there exists a way to transpose rows to columns for data in a table?
In particular I have the following table:
Table1
Field_1 Field_2 Field_3
a1 b1 c1
a1 b1 c2
a1 b1 c3
. . .
. . .
. . .
a1 b1 cn
a1 b2 d1
a1 b2 d2
a1 b2 d3
. . .
. . .
. . .
a1 b2 dn
a1 b3 e1
a1 b3 e2
a1 b3 e3
. . .
. . .
. . .
a1 b3 en
a1 b4 f1
a1 b4 f2
a1 b4 f3
. . .
. . .
. . .
a1 b4 fn
a1 b5 x1
a1 b5 x2
a1 b5 x3
. . .
. . .
. . .
a1 b5 xn
What I want is the following table:
Table2
Field_1 Field_2 Field_3 Field_4 Field_5 Field_6
a1 b1 b2 b3 b4 b5
a1 c1 d1 e1 f1 x1
a1 c2 d2 e2 f2 x2
a1 c3 d3 e3 fn x3
. . . . . .
. . . . . .
. . . . . .
a1 cn dn en fn xn
Note that for each value (b1,b2,b3,b4,b5) of field_2 in the Table1 there is an one to many relationship between that value of field_2 and the associate values in the field_3.
And the number n of (cn,dn,en,fn,xn) is variable.
Thanks in advance.
Enzo.
[Updated on: Sat, 04 June 2016 09:53] Report message to a moderator






Re: Transpose rows to columns without aggregation [message #652220 is a reply to message #652219] 
Sat, 04 June 2016 11:50 

dancko
Messages: 101 Registered: June 2013 Location: italy

Senior Member 


Hi
this is the working and complete Test case:
SCRIPT FOR CREATE TABLE:
CREATE TABLE ARTICLE
(
model varchar2(10 Char),
Sku varchar2(10 Char),
article varchar2(10 Char)
);
SCRIPT FOR INSERT DATA INTO TO ABOVE TABLE:
insert into ARTICLE values('m1','s01','a1');
insert into ARTICLE values('m1','s01','a2');
insert into ARTICLE values('m1','s01','a3');
insert into ARTICLE values('m2','s02','a4');
insert into ARTICLE values('m2','s02','a5');
insert into ARTICLE values('m2','s02','a6');
insert into ARTICLE values('m2','s02','a7');
insert into ARTICLE values('m2','s02','a8');
insert into ARTICLE values('m2','s03','a9');
insert into ARTICLE values('m2','s03','a10');
insert into ARTICLE values('m2','s03','a11');
insert into ARTICLE values('m2','s03','a12');
insert into ARTICLE values('m2','s03','a13');
insert into ARTICLE values('m2','s03','a14');
insert into ARTICLE values('m2','s03','a15');
insert into ARTICLE values('m2','s03','a16');
insert into ARTICLE values('m2','s03','a17');
insert into ARTICLE values('m3','s04','a18');
insert into ARTICLE values('m3','s04','a19');
insert into ARTICLE values('m3','s04','a20');
insert into ARTICLE values('m3','s04','a21');
insert into ARTICLE values('m3','s05','a22');
insert into ARTICLE values('m3','s05','a23');
insert into ARTICLE values('m3','s05','a24');
insert into ARTICLE values('m3','s05','a25');
insert into ARTICLE values('m3','s05','a26');
insert into ARTICLE values('m3','s05','a27');
insert into ARTICLE values('m3','s05','a28');
insert into ARTICLE values('m3','s06','a29');
insert into ARTICLE values('m3','s06','a30');
insert into ARTICLE values('m3','s07','a31');
insert into ARTICLE values('m3','s07','a32');
insert into ARTICLE values('m3','s07','a33');
insert into ARTICLE values('m3','s08','a34');
insert into ARTICLE values('m3','s08','a35');
insert into ARTICLE values('m3','s08','a36');
insert into ARTICLE values('m3','s08','a37');
What I desire is the following result:
OUTPUT TABLE:
Model Sku_1 Sku_2 Sku_3 Sku_4 Sku_5
m1 s01
m1 a1
m1 a2
m1 a3
m2 s02 s03
m2 a4 a9
m2 a5 a10
m2 a6 a11
m2 a7 a12
m2 a8 a13
m2 a14
m2 a15
m2 a16
m2 a17
m3 s04 s05 s06 s07 s08
m3 a18 a22 a29 a31 a34
m3 a19 a23 a30 a32 a35
m3 a20 a24 a33 a36
m3 a21 a25 a37
m3 a26
m3 a27
m3 a28
m3
m3
Note that the order of record on the Model field not have to necessarily that I have showed above...
The output table could be also this, for example:
OUTPUT TABLE:
Model Sku_1 Sku_2 Sku_3 Sku_4 Sku_5
m1 s01
m1 a1
m1 a2
m1 a3
m3 s04 s05 s06 s07 s08
m3 a18 a22 a29 a31 a34
m3 a19 a23 a30 a32 a35
m3 a20 a24 a33 a36
m3 a21 a25 a37
m3 a26
m3 a27
m3 a28
m2 s02 s03
m2 a4 a9
m2 a5 a10
m2 a6 a11
m2 a7 a12
m2 a8 a13
m2 a14
m2 a15
m2 a16
m2 a17
Thanks.
[Updated on: Sat, 04 June 2016 12:04] by Moderator Report message to a moderator






Re: Transpose rows to columns without aggregation [message #652224 is a reply to message #652223] 
Sat, 04 June 2016 12:04 

Michel Cadot
Messages: 67923 Registered: March 2007 Location: Nanterre, France, http://...

Senior Member Account Moderator 


SQL> col flag noprint
SQL> col rn noprint
SQL> break on model dup skip 1
SQL> with
2 data as (
3 select model, sku, article,
4 dense_rank() over (partition by model order by sku) rk,
5 row_number() over (partition by model, sku order by article) rn
6 from article
7 )
8 select 1 flag, 0 rn, model,
9 max(decode(rk, 1, sku)) sku_1,
10 max(decode(rk, 2, sku)) sku_2,
11 max(decode(rk, 3, sku)) sku_3,
12 max(decode(rk, 4, sku)) sku_4,
13 max(decode(rk, 5, sku)) sku_5
14 from data
15 group by model
16 union all
17 select 2, rn, model,
18 max(decode(rk, 1, article)) sku_1,
19 max(decode(rk, 2, article)) sku_2,
20 max(decode(rk, 3, article)) sku_3,
21 max(decode(rk, 4, article)) sku_4,
22 max(decode(rk, 5, article)) sku_5
23 from data
24 group by model, rn
25 order by 3, 1, 2
26 /
MODEL SKU_1 SKU_2 SKU_3 SKU_4 SKU_5
     
m1 s01
m1 a1
m1 a2
m1 a3
m2 s02 s03
m2 a4 a10
m2 a5 a11
m2 a6 a12
m2 a7 a13
m2 a8 a14
m2 a15
m2 a16
m2 a17
m2 a9
m3 s04 s05 s06 s07 s08
m3 a18 a22 a29 a31 a34
m3 a19 a23 a30 a32 a35
m3 a20 a24 a33 a36
m3 a21 a25 a37
m3 a26
m3 a27
m3 a28
[I fixed the result in your post]
[Updated on: Sat, 04 June 2016 12:54] Report message to a moderator




Goto Forum:
Current Time: Sun Aug 01 19:25:53 CDT 2021
