""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
   USING SQL TO COMBINE SEVERAL COLUMNS INTO ONE LIST
   """"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
   PRODUCT   :  R:BASE                  VERSION      :  3.1
   CATEGORY  :  SQL                     SUBCATEGORY  :  DATABASE DESIGN
   """"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
   At one time or another, you'll probably run into a situation where the
   database you're using doesn't have an ideal design.
 
   If it isn't possible to change the design, you can sometimes work
   around the limitations by using SQL. For example, you can use UNION
   SELECT in a SELECT command to combine data coming from several columns
   into one list.
 
 
   Design Problems
   """""""""""""""
   People new to database design may think it makes sense to create
   several columns for the same type of data. They're used to seeing data
   in spreadsheets instead of database tables.
 
   But if you split the same data into several columns (in worksheet
   fashion), you're probably putting information in column names when
   that information should be stored as data. You should redesign the
   database.
 
   For example, a new user might design a database as if it were a
   spreadsheet by making a column for each month's total sales. The table
   would have 13 columns: ACCTID, JANSALES, FEBSALES, MARSALES, and so
   on.  This is not a good relational design because there's data (the
   month name) in the column name. Databases should never have data
   stored in column names. It's better to make the database flexible by
   having three columns: ACCTID, DATE, SALES. Then you can create a
   summary report that shows sales for each month by ACCTID.
 
 
   If You Can't Change It, Join It
   """""""""""""""""""""""""""""""
   Sometimes, although the design should be modified, it just isn't
   possible. In these cases, you may be able to create a better design by
   creating a view of the data using UNION SELECT clauses in an SQL
   SELECT command.
 
   For example, say a group of physicians has a medical history database
   with a table named DIAGNOSIS that contains these nine columns:
 
     o  patientnum (INTEGER) patient identification number.
     o  birthdate (DATE) date of birth.
     o  sex (TEXT 4) patient gender.
     o  primesymptom (TEXT 16) primary symptom.
     o  secondsymptom (TEXT 16) secondary symptom.
     o  othersymptom (TEXT 16) third symptom if any.
     o  diagnosis (TEXT 20) diagnosis.
     o  treatment (TEXT 30) recommended treatment.
     o  doctorcode (INTEGER) doctor identification.
 
   The three symptom columns differentiate among what the doctors view as
   the most to least important symptoms of a patient's illness. In an
   ideal design, there would be one SYMPTOM column and another coded
   column (PRIME_CODE) to indicate whether it is a primary, secondary, or
   other symptom. Also, there would be two tables so that descriptive
   patient data (BIRTHDATE, SEX, DIAGNOSIS, TREATMENT, and DOCTORCODE)
   wouldn't be repeated in every symptom row.
 
 
   But the physicians wanted only one record for each patient, and only
   one table to hold everything. They were not willing to change the
   design even though a report could still show them everything at a
   glance.
 
 
   Example Data
   """"""""""""
   The DIAGNOSIS table might contain data like the data shown at the
   bottom of the page.
 
 
   Problem Report
   """"""""""""""
   The medical profession is an imperfect science; one doctor's diagnosis
   may differ from another's. Therefore, one of the doctors might want to
   see a list of all symptoms regardless of their primary, secondary, or
   other status. In addition to symptoms, the doctor wants to see
   diagnoses and treatments in a report that looks like the one shown on
   the right.
 
 
   Two-step SQL Solution: STEP 1
   """""""""""""""""""""""""""""
   Create a view to combine all three symptom columns into one column in
   the view. Use this CREATE VIEW command to create the view:
 
     CREATE VIEW symptoms +
       (symptom, diagnosis, treatment) +
       AS SELECT primesymptom, diagnosis, +
         treatment FROM diagnosis +
       UNION SELECT +
         secondsymptom, diagnosis, treatment +
         FROM diagnosis WHERE +
           secondsymptom IS NOT NULL +
       UNION SELECT +
         othersymptom, diagnosis, treatment +
         FROM diagnosis WHERE +
           othersymptom IS NOT NULL
 
   SYMPTOM, DIAGNOSIS, and TREATMENT are columns named in the view.
   SYMPTOM is a place holder for the three symptom columns coming from
   the DIAGNOSIS table.
 
   The first SELECT and the two UNION SELECTs make it possible for you to
   treat all three table columns as one view column. The two unions
   triple the number of rows in the view by appending the rows for the
   other two symptom columns.
 
 
   Two-step SQL Solution: STEP 2
   """""""""""""""""""""""""""""
   Create the report, and base it on the SYMPTOMS view. Set up a
   breakpoint on the SYMPTOM column. Then locate SYMPTOM on the break
   header (H1). Locate DIAGNOSIS and TREATMENT on a detail line (D). Then
   print the report.
 
   As shown here, SQL makes it possible to take a less-than-ideal design
   and create a flexible view of the data.