CODEWITHSUNDEEP
oracle-database
user2672165
user2672165

Reputation: 3049

Transposing one column into two columns

I thought I could do this easier than the solution I came up with. Therefore, I am asking whether it is possible to have a more simple solution to the following problem:

I have data on a single column format as follows:

    COLUMN 1
    =========
    partnumber
    version
    partnumber
    version
    partnumber
    version
    etc

Each item is identified by a partnumber and a version. The version of an item immediately follows the partnumber of the item. However, I would like the data in the following format:

COLUMN 1    COLUMN 2
========== =========
partnumber version
partnumber version
partnumber version
etc        etc

I came up with following two alternative queries:

with data as (
select col,rownum+mod(rownum,2) item_index,row_number() over (partition by rownum+mod(rownum,2) order by rownum ) colno from(
select -2141 col from dual
union all 
select 5 col from dual
union all
select -2102 col from dual
union all
select 2 col from dual
union all
select -2021 col from dual
union all
select 4 col from dual))
select t1.col,t2.col from (select col,item_index from data where colno=1) t1
join (select col,item_index from data where colno=2) t2
on t1.item_index=t2.item_index;


with data as (
select col,rownum rn from (
select -2141 col from dual
union all 
select 5 col from dual
union all
select -2102 col from dual
union all
select 2 col from dual
union all
select -2021 col from dual
union all
select 4 col from dual))
select t1.col,t2.col from (select col,rn+mod(rn,2) item_index from data where mod(rn,2)=1) t1
join (select col,rn+mod(rn,2) item_index from data where mod(rn,2)=0) t2
on t1.item_index=t2.item_index;

Is there a better alternative?

Upvotes: 0

Views: 58

Answers (2)

MT0
MT0

Reputation: 167822

Oracle Setup:

CREATE TABLE data(value) AS
  SELECT 'partnumber' FROM dual UNION ALL
  SELECT 'version'    FROM dual UNION ALL
  SELECT 'partnumber' FROM dual UNION ALL
  SELECT 'version'    FROM dual UNION ALL
  SELECT 'partnumber' FROM dual UNION ALL
  SELECT 'version'    FROM dual UNION ALL
  SELECT 'etc'        FROM dual UNION ALL
  SELECT 'etc'        FROM dual;

Query:

SELECT *
FROM   (
  SELECT value,
         CEIL( ROWNUM / 2 ) AS id,
         MOD( ROWNUM, 2 ) AS isPartNo
  FROM   DATA
)
PIVOT( MAX( value ) FOR isPartNo IN ( 1 AS PartNumber, 0 AS Version ) )
ORDER BY id;

Output:

        ID PARTNUMBER VERSION  
---------- ---------- ----------
         1 partnumber version    
         2 partnumber version    
         3 partnumber version    
         4 etc        etc

Upvotes: 2

Husqvik
Husqvik

Reputation: 5809

This looks a bit simpler to me:

WITH data(value) AS (
  SELECT 'partnumber' FROM dual UNION ALL
  SELECT 'version'    FROM dual UNION ALL
  SELECT 'partnumber' FROM dual UNION ALL
  SELECT 'version'    FROM dual UNION ALL
  SELECT 'partnumber' FROM dual UNION ALL
  SELECT 'version'    FROM dual UNION ALL
  SELECT 'etc'        FROM dual UNION ALL
  SELECT 'etc'        FROM dual),
seq_data(seq, value) AS (
  SELECT ROWNUM, value FROM data
)
SELECT
  t1.value column1, t2.value column2
FROM
  seq_data t1
  JOIN seq_data t2 ON t1.seq = t2.seq - 1
WHERE
  mod(t1.seq, 2) = 1

Upvotes: 1

Related Questions