Splitting the column data to two columns

Question

This is the actual output.

    DEPTNO ENAME             SAL
---------- ---------- ----------
        30 JAMES             950
        31 WARD             1250
        32 MARTIN           1250
        33 TURNER           1500
        34 ALLEN            1600
        35 BLAKE            2850

But I want split it like this for jasper report.

    DEPTNO ENAME             SAL        DEPTNO ENAME             SAL
---------- ---------- ----------    ---------- ---------- ----------
        30 JAMES             950            31 WARD             1250
        32 MARTIN           1250            33 TURNER           1500
        34 ALLEN            1600            35 BLAKE            2850

Please tell me that I have to change in query or in jasper components(I changed design already).

And I am using oracle. Thanks in advance.

Answer 1

try this:

with a(DEPTNO, ENAME, SAL, rn) as (
    select a.*, rownum rn from (
        select 30, 'JAMES', 950 from dual union 
        select 31, 'WARD',  1250 from dual union 
        select 32, 'MARTIN',1250 from dual union 
        select 33, 'TURNER',1500 from dual union 
        select 34, 'ALLEN', 1600 from dual union 
        select 35, 'BLAKE', 2850 from dual 
        -- union another 1 row to show you, what if EMP > even number
        union 
        select 39, 'JACK',  3850 from dual) a
        ) 

select a1.deptno,
       a1.ename, 
       a1.sal,
       a2.deptno,
       a2.ename, 
       a2.sal
  from a a1 left join a a2 on a1.rn + 1 = a2.rn
   where mod(a1.rn,2) = 1

output with some other data for test case:

    DEPTNO ENAME         SAL     DEPTNO ENAME         SAL
---------- ------ ---------- ---------- ------ ----------
        30 JAMES         950         31 WARD         1250 
        32 MARTIN       1250         33 TURNER       1500 
        34 ALLEN        1600         35 BLAKE        2850 
        39 JACK         3850                              

Answer 2

You cann't do that without dynamic sql. One of possible query is:

with t(DEPTNO, ENAME ,   SAL) as (
select        30, 'JAMES',             950 from dual union all
select        31, 'WARD',             1250 from dual union all
select        32, 'MARTIN',           1250 from dual union all
select        33, 'TURNER',           1500 from dual union all
select        34, 'ALLEN',            1600 from dual union all
select        35, 'BLAKE',            2850 from dual union all
select        36, 'MIKE',             2850 from dual),
w as (select DEPTNO, ENAME , SAL ,mod(rownum,3) as rn, rownum as ct from t)
select  
  EXTRACTVALUE(DEPTNO_ENAME_SAL_XML,'/PivotSet/item[1]/column[1]') as DEPTNO1,
  EXTRACTVALUE(DEPTNO_ENAME_SAL_XML,'/PivotSet/item[1]/column[2]') as ENAME1,
  EXTRACTVALUE(DEPTNO_ENAME_SAL_XML,'/PivotSet/item[1]/column[3]') as SAL1,
  EXTRACTVALUE(DEPTNO_ENAME_SAL_XML,'/PivotSet/item[2]/column[1]') as DEPTNO2,
  EXTRACTVALUE(DEPTNO_ENAME_SAL_XML,'/PivotSet/item[2]/column[2]') as ENAME2,
  EXTRACTVALUE(DEPTNO_ENAME_SAL_XML,'/PivotSet/item[2]/column[3]') as SAL2 from w
    pivot xml(max(ct) for (DEPTNO, ENAME , SAL) in(any,any,any))
order by DEPTNO1

As you can see I specify columns names. You can write your own procedure for generating column names but max column names in Oracle is 255