transpose rows to columns Oracle SQL 2

[speial]

I am creating a view that requires rows from a table. The rows in the table need to correspond to columns in the view.

Table Items:

Select isr_no, badge_nbr, model, serial_nbr, manu_cd 
from   items 
where  isr_no = 'mtr-10';

Result: (3 rows found in table, items)
isr_no badge_nbr model serial_nbr manu_cd

MTR-10 1234 RRT 98743QT INVENSYS
MTR-10 8986 RRT 082345X INVENSYS
MTR-10 2233 QSV 129Z45B LAND/G

++++++++++++++++++++++++++++++++++++++++++++

View being created:

Create or replace view  j_items_v 
(
 oi_badge_1
,oi_model_1
,oi_serial_nbr_1
,oi_manu_cd_1

,oi_badge_2
,oi_model_2
,oi_serial_nbr_2
,oi_manu_cd_2

,oi_badge_3
,oi_model_3
,oi_serial_nbr_3
,oi_manu_cd_3

)

AS
SELECT
 badge_nbr
,model
,serial_nbr
,manu_cd

from items
where isr_no = 'mtr-10';

I want to assign the first row returned to oi_badge_1 (and corresponding other columns),
The second row to oi_badge_2, and the third to oi_badge_3
accordingly. (the acutual order of the result set is not important. Just the first one retreived goes to the first assignment in the view.)

I know, as written, this will give an error that the number of columns do not match. After many atempts to make the assignmets, it just escapes me.

Your suggestions are appreciated.

Thanks in advance.

 

[cmmrfrds]

Select
(case when arow = 1 then badge_nbr else null end) as b1,
(case when arow = 1 then model else null end) as m1,
(case when arow = 1 then serial_nbr else null end) as s1,
(case when arow = 2 then badge_nbr else null end) as b2,
(case when arow = 2 then model else null end) as m2,
(case when arow = 2 then serial_nbr else null end) as s2,
etc.......

From
( /* an inline view to create a row number and order the way you want. example - window size is badge_nbr and order is by model */
SELECT
badge_nbr
,model
,serial_nbr
,manu_cd
,ROW_NUMBER () OVER (PARTITION BY badge_nbr ORDER BY model) as arow
from items
where isr_no = 'mtr-10'
)

Teak as necessary. Assuming Oracle 9 or higher.

 

[SantaMufasa]

[tt]Data: MTR-10 1234 RRT 98743QT INVENSYS

Query:...where isr_no = 'mtr-10';[/tt]

Be very careful about case when dealing with literals.


Here is code (from an angle different from cmmrfrds) that should also do what you want:

Create or replace view  j_items_v 
(
 oi_badge_1
,oi_model_1
,oi_serial_nbr_1
,oi_manu_cd_1
,oi_badge_2
,oi_model_2
,oi_serial_nbr_2
,oi_manu_cd_2
,oi_badge_3
,oi_model_3
,oi_serial_nbr_3
,oi_manu_cd_3
) AS
SELECT a.badge_nbr,a.model,a.serial_nbr,a.manu_cd
      ,b.badge_nbr,b.model,b.serial_nbr,b.manu_cd
      ,c.badge_nbr,c.model,c.serial_nbr,c.manu_cd
  from (select * from (select rownum rn, items.*
                         from items
                        where isr_no = 'MTR-10')) a
      ,(select * from (select rownum rn, items.*
                         from items
                        where isr_no = 'MTR-10')) b
      ,(select * from (select rownum rn, items.*
                         from items
                        where isr_no = 'MTR-10')) c
 where a.rn = 1 and b.rn = 2 and c.rn = 3
/

View created.

In the following SELECT against "j_items_v", I included SQL*Plus "COL" definitions just to spruce up the output...otherwise, they are optional statements:

col x5 format a5
col x7 format a7
col n5 format 99999
col x8 format a8
col oi_badge_1 heading "OI|Badge|1" like n5
col oi_model_1 heading "OI|Model|1" like x5
col oi_serial_nbr_1 heading "OI|Serial|Nbr 1" like x7
col oi_manu_cd_1 heading "OI|Man_CD|1" like x8
col oi_badge_2 heading "OI|Badge|2" like n5
col oi_model_2 heading "OI|Model|2" like x5
col oi_serial_nbr_2 heading "OI|Serial|Nbr 2" like x7
col oi_manu_cd_2 heading "OI|Man_CD|2" like x8
col oi_badge_3 heading "OI|Badge|3" like n5
col oi_model_3 heading "OI|Model|3" like x5
col oi_serial_nbr_3 heading "OI|Serial|Nbr 3" like x7
col oi_manu_cd_3 heading "OI|Man_CD|3" like x8
select * from j_items_v;

    OI OI    OI      OI           OI OI    OI      OI           
 Badge Model Serial  Man_CD    Badge Model Serial  Man_CD  
     1 1     Nbr 1   1             2 2     Nbr 2   2       ...
------ ----- ------- -------- ------ ----- ------- --------...
  1234 RRT   98743QT INVENSYS   8986 RRT   082345X INVENSYS...

1 row selected.
===============================================================

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[speial]

Thanks for the info. I will give it a try.