Visual Basic

Dim mylist As IQueryable(Of Myobject)
Dim ob1 As New List(Of Object)
Dim filter As New List(Of String)

If condition1 Then
    filter.Add("id = @" & ob1.Count)
    ob1.Add(idToFind)
End If

If condition2 Then
    filter.Add("field2 = @" & ob1.Count)
    ob1.Add(field2Value)
End If

...

Dim dynstr As String = String.Join(" AND ", filter)
mylist = context.MyObjects.Where(dynstr, ob1.ToArray()).ToList

SELECT
    T.Course,
    S.subject,
    SUM(CASE T.Adcat WHEN 'Reg' THEN 1 ELSE 0 END) As Reg,
    SUM(CASE T.Adcat WHEN 'Pri' THEN 1 ELSE 0 END) As Pri
FROM
    YourTable As T
    CROSS APPLY
    (
        VALUES (sub1), (sub2), (sub3), (sub4), (sub5)
    ) As S (subject)
GROUP BY
    T.Course,
    S.subject
ORDER BY
    T.Course,
    S.subject
;

DHARM PAL wrote:

but how the output comes to my application

Public Sub Show_SubjectSummary()
        sqlQRY = "SELECT * FROM tblstudetail where" _
             & "[session] = '2015' AND" _
             & "[course] = 'B.A. I' AND" _
            & "[ADstatus] LIKE 'OK'"
        ds = New DataSet
        da = New SqlDataAdapter(sqlQRY, Conn)
        da.Fill(ds, "tblstudetail")
        RDLCreportview.ReportViewer1.LocalReport.ReportEmbeddedResource = Application.StartupPath & "\Report\SubjectSummary.rdlc"
        sReportDataSource.Name = "DataSet1"
        sReportDataSource.Value = ds.Tables(0)
        RDLCreportview.ReportViewer1.LocalReport.ReportPath = Application.StartupPath & "\Report\SubjectSummary.rdlc"
        RDLCreportview.ReportViewer1.SetDisplayMode(DisplayMode.PrintLayout)
        RDLCreportview.ReportViewer1.RefreshReport()
        ds.Dispose()
        ds = Nothing
        RDLCreportview.ShowDialog()
        RDLCreportview.Dispose()
        Conn.Close()
    End Sub

da = new SqlDataAdapter("SELECT * FROM subsummary WHERE [course] = @Course", Conn)
da.SelectCommand.Parameters.AddWithValue("@Course", ddlCourse.SelectedValue)

ds = New DataSet()
da.Fill(ds, "subsummary")

sReportDataSource.Name = "DataSet1"
sReportDataSource.Value = ds.Tables(0)

RDLCreportview.ReportViewer1.LocalReport.ReportPath = Application.StartupPath & "\Report\SubjectSummary.rdlc"
RDLCreportview.ReportViewer1.SetDisplayMode(DisplayMode.PrintLayout)
RDLCreportview.ReportViewer1.RefreshReport()

ds.Dispose()
ds = Nothing

RDLCreportview.ShowDialog()
RDLCreportview.Dispose()

Public Sub Show_SubjectSummary()
        da = New SqlDataAdapter("SELECT * FROM viewsubsummary WHERE [course] = 'B.A. I'", Conn)
        'da.SelectCommand.Parameters.AddWithValue("@Course", cmbsession.SelectedValue)
        ds = New DataSet()
        da.Fill(ds, "viewsubsummary")
        sReportDataSource.Name = "DataSet1"
        sReportDataSource.Value = ds.Tables(0)
        RDLCreportview.ReportViewer1.LocalReport.ReportPath = Application.StartupPath & "\Report\SubjectSummary.rdlc"
        RDLCreportview.ReportViewer1.SetDisplayMode(DisplayMode.PrintLayout)
        RDLCreportview.ReportViewer1.RefreshReport()
        ds.Dispose()
        ds = Nothing
        RDLCreportview.ShowDialog()
        RDLCreportview.Dispose()
    End Sub

RDLCreportview.ReportViewer1.LocalReport.DataSources.Clear()
RDLCreportview.ReportViewer1.LocalReport.DataSources.Add(New ReportDataSource("DataSet1", ds.Tables(0)))

SELECT * FROM viewsubsummary WHERE [course] = 'B.A. I'

SELECT
    T.Course,
    S.subject,
    SUM(CASE T.Adcat WHEN 'Reg' THEN 1 ELSE 0 END) As Reg,
    SUM(CASE T.Adcat WHEN 'Pri' THEN 1 ELSE 0 END) As Pri
FROM
    YourTable As T
    CROSS APPLY
    (
        VALUES (sub1), (sub2), (sub3), (sub4), (sub5)
    ) As S (subject)
WHERE
    S.subject Is Not Null
And
    S.subject != ''
GROUP BY
    T.Course,
    S.subject
ORDER BY
    T.Course,
    S.subject
;

Course        subject        Reg                          Pri          
 
      BA1            HL           1                              1
 
                     EL           1                              0
 
                     SL           1                              0
 
                     Hlit         2                              1
 
                     His          1                              2
 
                     PS           1                              1
 
                     HS           1                              0
 
                     Socio        2                              0
	Count adcat	  2		                 1

DHARM PAL wrote:

how can i add statement to also count number of students in every category..

DHARM PAL wrote:

is there a way to add where clause based on column ADstatus and OLstatus AND EXstatus programetically