SQL - Omitting/Hide Specific Search Values

Question

I've been having an issue with reports at work lately that we need to pull data but omit a specific sport type from the results. We still need the names and other information to pull, just not a specific sport.

We have the report showing:

'Student' 'Address' 'Major' 'Hockey'
'Student' 'Address' 'Major' 'BSKB'
'Student' 'Address' 'Major' 'VLB'

But we need it to read:

'Student' 'Address' 'Major' 
'Student' 'Address' 'Major' 'BSKB'
'Student' 'Address' 'Major' 'VLB'

Is this actually something that can be done? My boss swears by it, but I feel that my classes told me it couldn't. It's a basic Select Query that we use, just with some in depth "Cases" to pull students based on major.

Thank you for any input!

Answer 1

I'm just guessing at the field names. Hope you understand the general idea:

SELECT student, address, major,
    CASE sporttype
    WHEN 'Hockey' THEN Null
    ELSE sporttype END AS sporttype
FROM sporttable

Answer 2

You can do what you want using the ROLAP 'datacube' operator

SELECT student, address, major,type
from table
group by cube (student, address, major, type)
having grouping(student)=0 and grouping(address)=0 and grouping(major)=0