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 Go to next message
dancko
Messages: 108
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 #652217 is a reply to message #652216] Sat, 04 June 2016 09:58 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
http://orafaq.com/wiki/PIVOT perhaps?

[Updated on: Sat, 04 June 2016 09:59]

Report message to a moderator

Re: Transpose rows to columns without aggregation [message #652218 is a reply to message #652217] Sat, 04 June 2016 10:08 Go to previous messageGo to next message
dancko
Messages: 108
Registered: June 2013
Location: italy
Senior Member
It works? ... if yes... how it works in my case?!
The PIVOT operation works when we have aggregation data... it's right?

[Updated on: Sat, 04 June 2016 10:30]

Report message to a moderator

Re: Transpose rows to columns without aggregation [message #652219 is a reply to message #652218] Sat, 04 June 2016 10:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have to use an aggregate function as a syntax requirement but if there is only one element in each group you aggregate nothing, so just group by something that is a key for your result.
In addition, PÏVOT does not just mean PIVOT clause but a method lie "transpose", did you read the link?

If you post a working and complete Test case: create table and insert statements along with the result you want with these data then we will work with your table and data otherwise it is most likely many won't even try to search for a solution to your question.
Post the result you want for the test case you post and explain this result.

Re: Transpose rows to columns without aggregation [message #652220 is a reply to message #652219] Sat, 04 June 2016 11:50 Go to previous messageGo to next message
dancko
Messages: 108
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 #652221 is a reply to message #652220] Sat, 04 June 2016 11:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Explain the output.
Why there are two "m1 a1" and no "m1 a3"?

Re: Transpose rows to columns without aggregation [message #652222 is a reply to message #652221] Sat, 04 June 2016 11:55 Go to previous messageGo to next message
dancko
Messages: 108
Registered: June 2013
Location: italy
Senior Member
Sorry.. it's "m1 a3". I correct now.
Re: Transpose rows to columns without aggregation [message #652223 is a reply to message #652222] Sat, 04 June 2016 11:58 Go to previous messageGo to next message
dancko
Messages: 108
Registered: June 2013
Location: italy
Senior Member
the system will not let me fix. The edit button is not abilitate. However It's the couple (m1 a3)

[Updated on: Sat, 04 June 2016 12:05]

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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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

Re: Transpose rows to columns without aggregation [message #652225 is a reply to message #652224] Sat, 04 June 2016 12:17 Go to previous message
dancko
Messages: 108
Registered: June 2013
Location: italy
Senior Member
it works... thanks a lot!!
Previous Topic: try to insert data in to table using procedure
Next Topic: Pattern recognition problem
Goto Forum:
  


Current Time: Thu Mar 28 11:45:02 CDT 2024