need query of wildly unormalized table

From: EdStevens <quetico_man_at_yahoo.com>
Date: Thu, 11 Oct 2007 08:46:11 -0700
Message-ID: <1192117571.918988.134700@o80g2000hse.googlegroups.com>

Platform: Oracle 10.2.0.1.0

Given a table with a structure something like this

key_field varchar2(10)

award_1    varchar2(4)
award_2    varchar2(4)
award_3    varchar2(4)

<snip repeatage>

award_30 varchar2(4)

The requirement is to report all 'key_field' where any of the 'award_*' columns contain a selected value. The crude way is

SELECT key_field
FROM .....
WHERE

  award_1 = 'AAAA'  or
  award_2 = 'AAAA'  or
  award_3 = 'AAAA'  or

<snip repeatage>

  award_30 = 'AAAA' or

Surely there is a more elegant way of dealing with this mess, but I'm having trouble getting my head wrapped around it. Unfortunately, normalizing the data (what a concept!) is beyond my control, other than the possibility of using a temp table.

Ideas? Received on Thu Oct 11 2007 - 10:46:11 CDT