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!

score 1 · Accepted Answer · answered Oct 10 '18 at 14:58

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

answered Oct 10 '18 at 14:58

Morten

398
1
6
16

Thank you very much! That makes sense to me. I'll go ahead and give it a try. – Jacob Kleiman Oct 10 '18 at 15:03
Depending on the size of the table query could you also use an IIF statement instead of a CASE statement. `SELECT student, address, major, IIF(Sporttype = "Hockey", Null, Sporttype) from sporttable` – Alex Oct 10 '18 at 15:03
The case ended up half working. I'm able to have the 'Hockey" disappear but none of the other sports will populate now. (Case when 'sport column' = 'HOCKE' then Null Else 'sport column' End) As 'sport column' – Jacob Kleiman Oct 10 '18 at 15:22
We did it! Thank you both so much for your help. – Jacob Kleiman Oct 10 '18 at 15:27
@JacobKleiman I'm glad you got it working now. I guess you figured out you had too many apostrophes at first try. If my answer was of any help to you, you can select it as a solution to your question. – Morten Oct 10 '18 at 18:28

score 0 · Answer 2 · answered Oct 10 '18 at 15:52

0

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

answered Oct 10 '18 at 15:52

DDS

2,340
16
34

SQL - Omitting/Hide Specific Search Values

2 Answers2