SQL & PL/SQL

Hi,

Sure, you can have any number of columns (within reason: maybe not 5000).

If you'd like help, post post some sample data (CREATE TABLE and insert statements), and the results you want from that data.

Hi All,

CREATE TABLE  T5 (idnum NUMBER,f1 NUMBER(10,5),f2 NUMBER(10,5),f3 NUMBER(10,5),e1 NUMBER(10,5),e2 NUMBER(10,5),h1 NUMBER(10,5),h2 NUMBER(10,5));

INSERT INTO T5 (IDNUM,F1,F2,F3,E1,E2,H1,H2) VALUES (1,'10.2004','5.009','7.330','9.008','8.003','.99383','1.43243');
INSERT INTO T5 (IDNUM,F1,F2,F3,E1,E2,H1,H2) VALUES (2,'4.2004','6.009','9.330','4.7008','4.60333','1.993','3.3243');
INSERT INTO T5 (IDNUM,F1,F2,F3,E1,E2,H1,H2) VALUES (3,'10.2040','52.6009','67.330','9.5008','8.003','.99383','1.43243');
INSERT INTO T5 (IDNUM,F1,F2,F3,E1,E2,H1,H2) VALUES (4,'9.20704','45.009','17.330','29.008','5.003','3.9583','1.243');

COMMIT;

select * from t5;

IDNUM	F1	F2	F3	E1	E2	H1	H2

1	10.2004	5.009	7.33	9.008	8.003	0.99383	1.43243
2	4.2004	6.009	9.33	4.7008	4.60333	1.993	3.3243
3	10.204	52.6009	67.33	9.5008	8.003	0.99383	1.43243
4	9.20704	45.009	17.33	29.008	5.003	3.9583	1.243



OUTPUT:
------
IDNUM	F1	E1	H1
1	10.2004	9.008	0.99383
1	5.009	8.003	1.43243
1	7.33		
2	4.2004	4.7008	1.993
2	6.009	4.60333	3.3243
2	9.33		
3	10.204	9.5008	0.99383
3	52.6009	8.003	1.43243
3	67.33		
4	9.20704	29.008	3.9583
4	45.009	5.003	1.243
4	17.33		

Thanks.

select * from (select t5.*,null E3,null h3 from t5)
unpivot ((F1,E1,H1) for c in((F1,E1,H1),
                             (F2,E2,H2),
                             (F3,E3,h3)));

Thanks for you reply.

what if e1 is date column? And not sure whether it's a null column. (some columns you have put value null)

Edited by: AJR on Aug 17, 2010 10:09 PM

To use to_char to corresponding data type.
Pivoting columns MUST be corresponding data type.
Because h3 and E3 don't exist.

Hi All,

Could anyone suggest an alternative?

Thanks.

SQL> select idnum
  2       , n
  3       , case n when 1 then f1 when 2 then f2 when 3 then f3 end f1
  4       , case n when 1 then e1 when 2 then e2 when 3 then null end e1
  5       , case n when 1 then h1 when 2 then h2 when 3 then null end h1
  6    from t5
  7       , (select level n from dual connect by level <= 3)
  8   order by idnum
  9       , n
 10  /

     IDNUM          N         F1         E1         H1
---------- ---------- ---------- ---------- ----------
         1          1    10.2004      9.008     .99383
         1          2      5.009      8.003    1.43243
         1          3       7.33
         2          1     4.2004     4.7008      1.993
         2          2      6.009    4.60333     3.3243
         2          3       9.33
         3          1     10.204     9.5008     .99383
         3          2    52.6009      8.003    1.43243
         3          3      67.33
         4          1    9.20704     29.008     3.9583
         4          2     45.009      5.003      1.243
         4          3      17.33

12 rows selected.

Regards,
Rob.

I like UnPivot :D

select *
from (select IDNUM,F1,F2,F3,E1,E2,H1,H2,
      null as E3,null as H3
      from T5)
UnPivot((F1,E1,H1) for sk in ((F1,E1,H1) as 1,
                              (F2,E2,H2) as 2,
                              (F3,E3,H3) as 3))
order by IDNUM,SK;

IDNUM  SK       F1       E1       H1
-----  --  -------  -------  -------
    1   1  10.2004    9.008   .99383
    1   2    5.009    8.003  1.43243
    1   3     7.33     null     null
    2   1   4.2004   4.7008    1.993
    2   2    6.009  4.60333   3.3243
    2   3     9.33     null     null
    3   1   10.204   9.5008   .99383
    3   2  52.6009    8.003  1.43243
    3   3    67.33     null     null
    4   1  9.20704   29.008   3.9583
    4   2   45.009    5.003    1.243
    4   3    17.33     null     null

Me too, but he wanted an alternative and the unpivot (11g) query had already been posted ;-)

OOPS I am not good at English.
I misunderstood meaning "alterNative" :8}