#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2004
    Location
    Alexandria, VA
    Posts
    186
    Rep Power
    15

    Normalize query for non-normalized table


    I am having to extract data from a table with 120 fields. It is the most non-normalized table I have ever had to work with. There are about 40 fields that are essentially "flags", and each record could have any combination of those fields set.

    What I need to end up with is a set of records where each set field becomes a text field in an individual row in the results. For example, assuming the table looked like this:

    Code:
    Name varchar(32)
    Field1 char(1)
    Field2 char(1)
    Field3 char(1)
    Field4 char(1)
    And the data looked like this:
    Code:
    Name	Field1	Field2	Field3	Field4
    George	0	1	1	0
    Frank	0	0	1	1

    I want the results to look like this:

    Code:
    Name	Value
    George	HasField2
    George	HasField3
    Frank	HasField3
    Frank	HasField4
    Any suggestions?

    Thanks...
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,382
    Rep Power
    391
    Code:
    select name,
           'HasColumn1'
      from t
     where Column1 = '1'
     union all
    select name,
           'HasColumn2'
      from t
     where Column2 = '1'
     union all
    ...
     order
        by name
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,207
    Rep Power
    4279
    notice how in his masterfully understated and subtle way, swampboogie is reminding you that tables have columns, not fields

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2004
    Location
    Alexandria, VA
    Posts
    186
    Rep Power
    15
    I was hoping there was a better way then having to do a massive union query. Oh well...

    and btw - I cut my teeth on dBase III, back when they were called "fields". Old habits die hard.

IMN logo majestic logo threadwatch logo seochat tools logo