CODEWITHSUNDEEP
sqlt-sql
Eric J.
Eric J.

Reputation: 150108

Build select result based on multiple column values

I have a Person table with multiple columns indicating the person's stated ethnicity (e.g. African American, Hispanic, Asian, White, etc). Multiple selections (e.g. White and Asian) are allowed. If a particular ethnicity is selected the column value is 1, if it is not selected it is 0, and if the person skipped the ethnicity question entirely it is NULL.

I wish to formulate a SELECT query that will examine the multiple Ethnicity columns and return a single text value that is a string concatenation based on the columns whose values is 1. That is, if the column White is 1 and the column Asian is 1, and the other columns are 0 or NULL, the output would be 'White / Asian'.

One approach would be to build a series of IF statements that cover all combinations of conditions. However, there are 8 possible ethnicity responses, so the IF option seems very unwieldy.

Is there an elegant solution to this problem?

Upvotes: 1

Views: 136

Answers (3)

HABO
HABO

Reputation: 15816

-- Some sample data.
declare @Persons as Table ( PersonId Int Identity,
  AfricanAmerican Bit Null, Asian Bit Null, Hispanic Bit Null, NativeAmerican Bit Null, White Bit Null );
insert into @Persons ( AfricanAmerican, Asian, Hispanic, NativeAmerican, White ) values
  ( NULL, NULL, NULL, NULL, NULL ),
  ( 0, 0, 0, 0, 0 ),
  ( 1, 0, 0, 0, 0 ),
  ( 0, 1, 0, 0, 0 ),
  ( 0, 0, 1, 0, 0 ),
  ( 0, 0, 0, 1, 0 ),
  ( 0, 0, 0, 0, 1 ),
  ( 0, 1, 1, 1, NULL );

-- Display the results.
select PersonId, AfricanAmerican, Asian, Hispanic, NativeAmerican, White,
  Substring( Ethnicity, case when Len( Ethnicity ) > 3 then 3 else 1 end,
    case when Len( Ethnicity ) > 3 then Len( Ethnicity ) - 2 else 1 end ) as Ethnicity
  from (
  select PersonId, AfricanAmerican, Asian, Hispanic, NativeAmerican, White,
    case when AfricanAmerican = 1 then ' / African American' else '' end +
    case when Asian = 1 then ' / Asian' else '' end +
    case when Hispanic = 1 then ' / Hispanic' else '' end +
    case when NativeAmerican = 1 then ' / Native American' else '' end +
    case when White = 1 then ' / White' else '' end as Ethnicity
    from @Persons
  ) as Simone;

Upvotes: 0

Kiril Rusev
Kiril Rusev

Reputation: 753

This is an efficient way of doing it:

ISNULL(
NULLIF(
    STUFF(
        CASE WHEN AfricanAmerican ='1' THEN 'AfricanAmerican/' ELSE '' END
        + CASE WHEN White='1'  THEN 'White/' ELSE '' END
        + CASE WHEN Asian='1' THEN 'Asian' ELSE '' END
        , 1, 2, '')
        ,'')
    , '')
  As Ethnicity

Upvotes: 0

madturbocow
madturbocow

Reputation: 119

Assuming SQL Server it would be this. 

select case AfricanAmerican when 1 then 'African American/' else '' end
        + case White when 1 then 'White/' else '' end
        + case Hispanic when 1 then 'Hispanic/' else '' end
    from PersonTable

Upvotes: 2

Related Questions