Re: A tricky SQL statement

From: Paul Austin <paustin_at_gw.ford.com>
Date: 1997/01/30
Message-ID: <32F11ABA.53DE@gw.ford.com>#1/1

> <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

Something like this should work (I haven't tested it)

SELECT
  sident,

  COUNT(DECODE(mident,'B1',mident,0)) "B1",
  COUNT(DECODE(mident,'B2',mident,0)) "B2",
  COUNT(DECODE(mident,'L1',mident,0)) "L1",
  COUNT(DECODE(mident,'RF',mident,0)) "RF"

GROUP BY
  sident

>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

It would also be possible to build this query dynamically by finding out the unique values of ident and then building up the query from that.

Hope this helps,
regards,
paul

---

Paul Austin, Analyst, WPPS Supplier Data. 40/432, Trafford House, 8 Station Way, Basildon, Essex SS16 5XX PROFS: PAUSTIN, INTERNET: paustin_at_gw.ford.com, UNI: pda1_at_ukc.ac.uk Tel.44-1268-703016/718-3016 Fax.44-1268-703673/718-3673 Received on Thu Jan 30 1997 - 00:00:00 CST