Re: How do I transform a row into columns ?

From: MarkyG <markg_at_mymail.tm>
Date: 4 Feb 2002 02:42:12 -0800
Message-ID: <ab87195e.0202040242.2a8e3485@posting.google.com>

Hi,

This post is like a reverse of a common one which pops up on this NG occasionally, this time you want rows from columns.

Below is code I tried, it uses the view USER_TAB_COLUMNS, a quadruple nested DECODE and a deliberate cartesian join to produce results. I created a temp table for myself to get some test data based on what you posted. Hope it helps.

---

create table temp
(xx number,

 a number,
 b number,
 c number,

 d number);

insert into temp values (1,4,3,2,6);
insert into temp values (2,5,3,1,8);
insert into temp values (3,6,4,2,7);

commit;

SELECT
   a.xx,
   b.column_name name,
   DECODE(b.column_name, 'A', a.a,
    DECODE(b.column_name, 'B', a.b,

     DECODE(b.column_name, 'C', a.c,
      DECODE(b.column_name, 'D', a.d)))) value

FROM temp a, user_tab_columns b
WHERE b.column_name in ('A','B','C','D');

SQL>

        XX NAME                                VALUE
---------- ------------------------------ ----------
         1 A                                       4
         1 B                                       3
         1 C                                       2
         1 D                                       6
         2 A                                       5
         2 B                                       3
         2 C                                       1
         2 D                                       8
         3 A                                       6
         3 B                                       4
         3 C                                       2
         3 D                                       7

------------------------------------------------------------

Mark

"Waldemar Rucha" <waldemar.rucha_at_pdb.sbs.de> wrote in message news:<a3do1g$6kd$1_at_news.mch.sbs.de>...
> Hello,
>
> I've a view with 5 columns
> XX A B C D
> ----------------------
> 1 4 3 2 6
> 2 5 3 1 8
> 3 6 4 2 7
>
> and I need a result like
>
> XX Name Value
> -------------------
> 1 A 4
> 1 B 3
> 1 C 2
> 1 D 6
> 2 A 5
> 2 B 3
> 2 C 1
> ... and so on.
>
> I tried with UNION ALL and 4 selects but it very slow (some parts are
> remote).
> Any other ideas???
>
> Very thanks and regards
> Waldemar
Received on Mon Feb 04 2002 - 04:42:12 CST