Re: A tricky SQL statement

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

I received e-mail from several people saying that my solution returns rows as if I had done a UNION of SQL that looks for each value (four rows instead of one) and asking how to get them in one row. I am sorry, the query in my reply was just an idea how to pivot table more efficiently. In order to get one row per Sident you simply need to group by:

  SELECT Sident,

          SUM(DECODE(Mident,'B1',M00,NULL)) B1,
          SUM(DECODE(Mident,'B2',M00,NULL)) B2,
          SUM(DECODE(Mident,'L1',M00,NULL)) L1,
          SUM(DECODE(Mident,'RF',M00,NULL)) RF

    FROM Your_Table
    GROUP BY Sident;

Solomon.Yakobson_at_entex.com

In article <854635562.30734_at_dejanews.com>,   Solomon.Yakobson_at_entex.com wrote:
>
> Yes, ORACLE does not provide any means to pivot table as it was
> mentioned by Dan Rippel. Although if you know all the values in
> MIDENT column you are interested in, solution is much eazier with
> much better performance than one suggested by Chris Halioris.
>
> SELECT Sident,
> DECODE(Mident,'B1',M00,NULL) B1,
> DECODE(Mident,'B2',M00,NULL) B2,
> DECODE(Mident,'L1',M00,NULL) L1,
> DECODE(Mident,'RF',M00,NULL) RF
> FROM Your_Table;
>
> Solomon.Yakobson_at_entex.com
>
> In article <32EF4DE3.7D54_at_vegdir.vegvesen.no>,
> Anders Hattestad <anders.hattestad_at_vegdir.vegvesen.no> wrote:
> >
> > I have a table that looks like this:
> >
> > sident mident dato m00
> > M201 B1 07-16-95 2983
> > M201 B2 07-16-95 2996
> > M201 L1 07-16-95 2956
> > M201 RF 07-16-95 3
> > M201 VH 07-16-95 1
> > M201 VR 07-16-95 33
> > M251 B1 11-22-93 2555
> > M251 L1 11-22-93 2535
> > M251 RF 11-22-93 88
> > M251 VH 11-22-93 6
> > M251 VR 11-22-93 7
> > M253 B1 11-30-93 2658
> >
> > And I would like to extract it on this form:
> >
> > Sident B1 B2 L1 RF
> > M201 2983 2996 2956 3
> > M251 2535 88
> > M253 2658
> >
> > Have any of you a solution on how this sql statement should look like?
> >
> > Thanks in advances
> > Anders
> -------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet

Received on Mon Feb 03 1997 - 00:00:00 CST