Group by multiple bit or conditionally render using crystal and SQL-CodePudding

I have a table where I have students name, their ID and a bit to signify whether or not they passed a certain subject. Ultimately, I would like to group by all the subjects in 1 report / query where individuals have passed. I couldnt figure out how to script this so I just did a select * and elected to try in crystal reports.

This is my Data.

ID	Name	passMath	passScience	passFrench
1	Peter	1	0	1
2	John	1	1	1
3	Kacy	0	1	1

And this is the expected results, or what I am ultimately trying to show in a report. Basically, in my crystal report, I'll have a secion
Math

Peter

Science

John

Kacy

French

Peter

John

Kacy

What have I tried

Now, I haven't figured out how to do this in a query, so what I did is, following the answer from this link I tried to conditionally render a section.

My issue is, this sort of works but the results it's returning is.

passMath

Peter

passScience

John

passMath

John

Basically, instead of grouping everyone who passed math together, it's giving them their separate row. So John and Peter both create a section titled "passMath" where I want them to be grouped together. What I did in crystal report was to create a section>section expert and add {usp_student.passMath}= false to the formula area where I expect it to suppress all records that didnt pass Math. So far it works 50% And I would like it to group them all.

Appreciate if I could get help with this query or a way to fix the crystal report.

CodePudding user response：

I'm not sure if this is what you're after but I suspect it may help:

with subjects as (
  select * from (values('Math'), ('Science'), ('French'))s(Subject)
)
select Subject, Name 
from Subjects s
join t on 
    (s.subject = 'Math' and t.passMath = 1) or
    (s.subject = 'Science' and t.passScience = 1) or
    (s.subject = 'French' and t.passFrench = 1)
order by Subject;

CodePudding user response：

If you don't want to resort to the type of cartesian product suggested by Stu, two alternatives are:

Use a UNION statement to concatenate the passing cases for the 3 scenarios.

In Crystal, simply insert 3 subreports, each showing the students passing one of the targeted subjects.