Re: A tricky SQL statement

Anders Hattestad 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?

The following statemnt produces what you requested.

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'

You obviously need to replace tablename with the name of your table. The other constraint is that you must hardcode the values of mident into the select statement as I did above. You then join the table to itself as many times as you have different values for mident. If you have an unlimited number of valuse for mident and want to report on each of them then you must take a different approach.

HTH,
Chris Halioris
Tactics, Inc. Received on Wed Jan 29 1997 - 00:00:00 CST