Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: March 03, 2018 - 6:20 am UTC

Last updated: March 07, 2018 - 1:24 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hai, Tom sir...I have table with columns like this...I have 6 columns in my table with data in below.I need to write
an SQL code to get the in below output.Out i mentioned at the ending.Please look at and give me the sol.


Create table t3
(

col1 date,
col2 varchar2(10),
col3 number,
col4 varchar2(10),
col5 number,
col6 varchar2(10)
)

;

Insert into t3 values(to_date('02-05-2018','dd-mm-yyyy'),'IZ231',9,'jan',27,'jan');

Insert into t3 values(to_date('02-05-2018','dd-mm-yyyy'),'IZ232',8,'Mar',99,'Mar');



select * from t3

;

COL1 COL2 COL3 COL4 COL5 COL6
02-MAY-18 IZ231 9 jan 27 jan
02-MAY-18 IZ232 8 Mar 99 Mar


Here in below is my output

Output
*******

02-05-2018 IZ231 9 jan
02-05-2018 IZ231 27 Jan
02-05-2018 IZ232 8 Mar
02-05-2018 IZ232 99 Mar

Note:-Write an SQL code here to get my above Output.

Thanks In advance.

and Connor said...

SQL> Create table t3
  2  (
  3  col1 date,
  4  col2 varchar2(10),
  5  col3 number,
  6  col4 varchar2(10),
  7  col5 number,
  8  col6 varchar2(10)
  9  )
 10  ;

Table created.

SQL>
SQL> Insert into t3 values(to_date('02-05-2018','dd-mm-yyyy'),'IZ231',9,'jan',27,'jan');

1 row created.

SQL> Insert into t3 values(to_date('02-05-2018','dd-mm-yyyy'),'IZ232',8,'Mar',99,'Mar');

1 row created.

SQL> select * from t3;

COL1      COL2             COL3 COL4             COL5 COL6
--------- ---------- ---------- ---------- ---------- ----------
02-MAY-18 IZ231               9 jan                27 jan
02-MAY-18 IZ232               8 Mar                99 Mar

SQL>
SQL>
SQL>
SQL> select col1, col2, col3, col4
  2  from t3
  3  union all
  4  select col1, col2, col5, col6
  5  from t3
  6  order by 1,2,3;

COL1      COL2             COL3 COL4
--------- ---------- ---------- ----------
02-MAY-18 IZ231               9 jan
02-MAY-18 IZ231              27 jan
02-MAY-18 IZ232               8 Mar
02-MAY-18 IZ232              99 Mar


Rating

  (3 ratings)

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

Comments

Naga sudhakara, March 04, 2018 - 4:56 am UTC

Thank you, but in case if I have 1000 columns in my table, it is tuff thing to type all columns in between select statement.Can I get any random solution for the same output. THanks.
Connor McDonald
March 06, 2018 - 2:17 am UTC

If you have 1000 columns I'd be asking *other* questions. The first of which being - why ?

Take a look here

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

lazy

Racer I., March 05, 2018 - 12:50 pm UTC

Hi,

select * from t3
unpivot ((a,b) for ct in ((col3,col4) as 1, (col5,col6) as 2))

and to generate the column-list :

select listagg(x, ', ') WITHIN GROUP (ORDER BY pos) line from (
select pos, '(col' || (pos + 1) || ',col' || (pos + 2) || ') as ' || (pos / 2) x
from (select rownum pos from dual connect by rownum <= 4)
where MOD(pos, 2) = 0)

but 1000 columns might exceed listagg-limit (4000).

regards,

Just unpivoting?

Duke Ganote, March 06, 2018 - 5:19 pm UTC

Manipulating 1000 columns is just manipulating the metadata in order to generate the SQL, no?

WITH
callem AS ( 
SELECT column_Name, column_id
     , mod(column_id+1,2)+1 as o# 
     , trunc((column_id-1)/2)+1 r#
     , dense_rank()over(order by trunc((column_id-1)/2)+1 DESC ) dr#
     , ROW_NUMBER()OVER(ORDER BY column_id DESC) rr#
  FROM user_tab_columns 
 WHERE table_name = 'T3'
 ORDER BY 2 
)
select 'SELECT col1, col2, '|| max(case when o# = 1 then column_Name end)
                       ||','|| max(case when o# = 2 then column_Name end)
               ||' FROM t3 '|| max(case when dr# > 1 then 'UNION ALL' end) as stmt
  from callem c
 where r# > 1
 group by r#
 order by r#;

STMT
-------------------------------------------
SELECT col1, col2, COL3,COL4 FROM t3 UNION ALL
SELECT col1, col2, COL5,COL6 FROM t3


Which is easily executed:

SELECT col1, col2, COL3,COL4 FROM t3 UNION ALL
SELECT col1, col2, COL5,COL6 FROM t3;

COL1       COL2   COL3  COL4
---------  -----  ----  ----
02-MAY-18  IZ231     9  jan
02-MAY-18  IZ232     8  Mar
02-MAY-18  IZ231    27  jan
02-MAY-18  IZ232    99  Mar

Connor McDonald
March 07, 2018 - 1:24 am UTC

True...but I'm still at this point :-)

If you have 1000 columns I'd be asking *other* questions. The first of which being - why ?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.