Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: A tricky sql question

Re: A tricky sql question

From: <Solomon.Yakobson_at_entex.com>
Date: 1997/02/04
Message-ID: <855067512.3051@dejanews.com>#1/1

Once again, ORACLE does not provide any means to pivot table. Although if you you are interested in a cpecific number of columns (t00, t01 and t02) and a specific number of column Mident values ('L1' and 'ND'), solution could be:

  SELECT Sident,

          't00' NR
          SUM(DECODE(Mident,'L1',t00,NULL)) L1,
          SUM(DECODE(Mident,'ND',t00,NULL)) ND,
    FROM Your_Table
    GROUP BY Sident,

             't00'
 UNION ALL
  SELECT Sident,

          't01' NR
          SUM(DECODE(Mident,'L1',t01,NULL)) L1,
          SUM(DECODE(Mident,'ND',t01,NULL)) ND,
    FROM Your_Table
    GROUP BY Sident,

             't01'
 UNION ALL
  SELECT Sident,

          't02' NR
          SUM(DECODE(Mident,'L1',t02,NULL)) L1,
          SUM(DECODE(Mident,'ND',t02,NULL)) ND,
    FROM Your_Table
    GROUP BY Sident,

             't02';

Solomon.Yakobson_at_entex.com

In article <32F5FE1A.47CE_at_vegdir.vegvesen.no>,   Anders Hattestad <anders.hattestad_at_vegdir.vegvesen.no> wrote:
>
> Thanks for all the input and answers on my last question.
>
> The results can be summarized like this.
> Method 1)
> select sident
> ,SUM( DECODE( mident, 'B1', m00, NULL)) B1
> ,SUM( DECODE( mident, 'B2', m00, NULL) ) B2
> ,SUM( DECODE( mident, 'L1', m00, NULL) ) L1
> FROM ???your_table???
> GROUP BY sident;
>
> Method 2)
> SELECT a.sident, a.m00, b.m00, c.m00, d.m00 FROM tablename a, tablename b,
 tablename c, tablename d
> WHERE b.sident(+) = a.sident
> AND c.sident(+) = a.sident
> AND d.sident(+) = a.sident
> AND a.mident(+) = 'B1'
> AND b.mident(+) = 'B2'
> AND c.mident(+) = 'L1'
> AND d.mident(+) = 'RF'
>
> But I have a similar problem now.
> sident mident t00 t01 t02
> M244 L1 2653 2652 2648
> M777 L1 2629 2635 2641
> M777 ND 3 41 0
> M244 ND 2682 2683 2681
>
> And I would like it on this format
> Sident NR L1 ND
> M244 t00 2653 2682
> M244 t01 2652 2683
> M244 t02 2648 2681
> M777 t00 2653 3
> M777 t01 2652 41
> M777 t02 2641 0
>
> I have tried and read every referens I have found without results.
> Any tips would be appraised.
>
> Anders Hattestad

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet
Received on Tue Feb 04 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US