0

Example data rows:

| ID  | First Name | Last Name | Federal Race Code |
| 101 | Bob        | Miller    | 01010             |
| 102 | Daniel     | Smith     | 00011             |

The "Federal Race Code" field contains binary data, and each "1" is used to determine if a particular check box is set on a particular web form. E.g., the first bit is American Indian, second bit is Asian, third bit is African American, fourth is Pacific Islander, and the fifth is White.

I need to generate a separate row for each bit that is set to "1". So, given the example above, I need to generate output that looks like this:

| ID  | First Name | Last Name | Mapped Race Name  |
| 101 | Bob        | Miller    | Asian             |
| 101 | Bob        | Miller    | African American  |
| 102 | Daniel     | Smith     | Pacific Islander  |
| 102 | Daniel     | Smith     | White             |

Any tips or ideas on how to go about this?

Griven
  • 183
  • 3
  • 12
  • 1
    that'd be an "unpivot" query: http://stackoverflow.com/questions/1128737/unpivot-and-postgresql – Marc B Sep 05 '14 at 20:36
  • possible duplicate of [Create a row for each cell that has data from multiple columns](http://stackoverflow.com/questions/25682396/create-a-row-for-each-cell-that-has-data-from-multiple-columns) – Bulat Sep 05 '14 at 20:37

1 Answers1

1

You can do it with either 6 queries with UNION or one UNPIVOT clause.

In any case you should start by splitting that binary logic into 6 columns:

SELECT *, 
 CASE WHEN federal_race_code & 16 = 16 THEN 1 ELSE 0 END as NativeAmerican,
 ..
 CASE WHEN federal_race_code & 1 = 1 THEN 1 ELSE 0 END as White
FROM myTable

Then UNION:

SELECT *, 'Native American' AS Race 
FROM (<subquery>)
WHERE NativeAmerican = 1
UNION
...
UNION
SELECT *, 'White' AS Race 
FROM (<subquery>)
WHERE White = 1

If you are on Oracle or SQL server use CTE.

Bulat
  • 6,869
  • 1
  • 29
  • 52
  • I ended up splitting it into six columns in another table (OpenEdge Progress -> Bulk insert into my own SQL cache), and then did a series of unions. It may not be as elegant as the unpivot, but it was a heck of a lot easier. Thanks for your help! – Griven Sep 05 '14 at 21:28