How to create a cross tab (in crystal) from multiple columns (in sql)

Question

I have 5 columns in SQL that I need to turn into a cross tab in Crystal.

This is what I have:

 Key  | RELATIONSHIP    | DISABLED | LIMITED | RURAL | IMMIGRANT
-----------------------------------------------------------------
  1   | Other Dependent |   Yes    |   No    |  No   |   No
  2   | Victim/Survivor |   No     |   No    |  No   |   No
  3   | Victim/Survivor |   Yes    |   No    |  No   |   No
  4   | Child           |   No     |   No    |  No   |   No
  5   | Victim/Survivor |   No     |   No    |  No   |   No
  6   | Victim/Survivor |   No     |   No    |  No   |   No
  7   | Child           |   No     |   No    |  No   |   No
  8   | Victim/Survivor |   No     |   Yes   |  Yes  |   Yes
  9   | Child           |   No     |   Yes   |  Yes  |   Yes
  10  | Child           |   No     |   Yes   |  Yes  |   Yes

This is what I want the cross tab to look like (Distinct count on Key):

               | Victim/Survivor | Child | Other Dependent | Total |
    --------------------------------------------------------------
   | DISABLED  |       1         |   0   |        1        |   2   |
    --------------------------------------------------------------
   | LIMITED   |       1         |   2   |        0        |   3   |
    --------------------------------------------------------------    
   | RURAL     |       1         |   2   |        0        |   3   |
    --------------------------------------------------------------    
   | IMMIGRANT |       1         |   2   |        0        |   3   |
    --------------------------------------------------------------    
   | TOTAL     |       4         |   6   |        1        |   11  |
    --------------------------------------------------------------

I used this formula in Crystal in an effort to combine 4 columns (Field name = {@OTHERDEMO})...

IF {TABLE.DISABLED} = "YES" THEN "DISABLED"  ELSE
IF {TABLE.LIMITED} = "YES" THEN "LIMITED" ELSE
IF {TABLE.IMMIGRANT} = "YES" THEN "IMMIGRANT" ELSE
IF {TABLE.RURAL} = "YES" THEN "RURAL"

...then made the cross-tab with @OTHERDEMO as the rows, RELATIONSHIP as the Columns with a distinct count on KEY:

Problem is, once crystal hits the first "Yes" it stops counting thus not categorizing correctly in the cross-tab. So I get a table that counts the DISABILITY first and gives the correct display, then counts the Limited and gives some info, but then dumps everything else.

In the past, I have done mutiple conditional formulas...

IF {TABLE.DISABLED} = "YES" AND {TABLE.RELATIONSHIP} = "Victim/Survivor" THEN {TABLE.KEY} ELSE {@NULL} 
(the @null formula is because Crystal, notoriously, gets confused with nulls.)

...then did a distinct count on Key, and finally summed it in the footer.

I am convinced there is another way to do this. Any help/ideas would be greatly appreciated.

Answer 1

If you unpivot those "DEMO" columns into rows it will make the crosstab super easy...

select 
  u.[Key], 
  u.[RELATIONSHIP], 
  u.[DEMO]
from 
  Table1
  unpivot (
    [b] for [DEMO] in ([DISABLED], [LIMITED], [RURAL], [IMMIGRANT])
  ) u
where
    u.[b] = 'Yes'

SqlFiddle

or if you were stuck on SQL2000 compatibility level you could manually unpivot the Yes values...

select [Key], [REALTIONSHIP], [DEMO] = cast('DISABLED' as varchar(20)) 
from Table1 
where [DISABLED] = 'Yes'
union
select [Key], [REALTIONSHIP], [DEMO] = cast('LIMITED' as varchar(20)) 
from Table1 
where [LIMITED] = 'Yes'
union
select [Key], [REALTIONSHIP], [DEMO] = cast('RURAL' as varchar(20)) 
from Table1 
where [RURAL] = 'Yes'
union
select [Key], [REALTIONSHIP], [DEMO] = cast('IMMIGRANT' as varchar(20)) 
from Table1 
where [IMMIGRANT] = 'Yes'

For the crosstab, use a count on the Key column (aka row count), [DEMO] on rows, and [RELATIONSHIP] on columns.