Hi hazel14,
In order to get combined data from database you need to make use of Pivot clause.
Stored Procedure
CREATE PROCEDURE GetStudentMarks
@Semester VARCHAR(10)
AS
BEGIN
CREATE TABLE #Students (
MatricNo INT PRIMARY KEY,
Name VARCHAR(100)
)
INSERT INTO #Students VALUES(888999, 'Hazel')
CREATE TABLE #Marks(
MatricNo INT,
Semester INT,
Test VARCHAR(50),
Marks INT
)
INSERT INTO #Marks VALUES(888999, 1, 'Assignment_1', 15)
INSERT INTO #Marks VALUES(888999, 1, 'Assignment_2', 10)
INSERT INTO #Marks VALUES(888999, 1, 'Presentation', 10)
INSERT INTO #Marks VALUES(888999, 1, 'Project', 20)
INSERT INTO #Marks VALUES(888999, 2, 'Assignment_1', 25)
INSERT INTO #Marks VALUES(888999, 2, 'Presentation', 20)
INSERT INTO #Marks VALUES(888999, 2, 'Project', 30)
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName = ISNULL(@ColumnName + ',', '') + QUOTENAME(Test) FROM (SELECT DISTINCT [Test] FROM #Marks WHERE Semester = @Semester) AS [Test]
DECLARE @ColumnForSum AS NVARCHAR(MAX)
SELECT @ColumnForSum = REPLACE(@ColumnName,',','+')
SELECT @ColumnForSum = REPLACE(@ColumnForSum,'[','ISNULL([')
SELECT @ColumnForSum = REPLACE(@ColumnForSum,']','],0)')
SET @DynamicPivotQuery = 'SELECT MatricNo, Name, '+@ColumnName+', SUM('+@ColumnForSum+') Final
FROM
(
SELECT m.MatricNo, s.Name, m.Test, m.Marks
FROM #Students s
INNER JOIN #Marks m ON s.MatricNo = m.MatricNo
WHERE Semester = '+@Semester+'
)t
PIVOT
(
MAX([Marks]) FOR Test IN ('+@ColumnName+')
)piv GROUP BY MatricNo,Name,'+@ColumnName+''
EXEC (@DynamicPivotQuery)
DROP TABLE #Students
DROP TABLE #Marks
END
HTML
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<asp:TextBox ID="txtSemester" runat="server"></asp:TextBox>
<asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="OnSubmit" />
<br />
<br />
<asp:GridView ID="gvStudent" runat="server"></asp:GridView>
</form>
</body>
</html>
Code
C#
protected void OnSubmit(object sender, EventArgs e)
{
string semester = txtSemester.Text.Trim();
string spName = "GetStudentMarks";
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(spName, con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Semester", semester);
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
gvStudent.DataSource = dt;
gvStudent.DataBind();
}
}
}
}
}
VB.Net
Protected Sub OnSubmit(ByVal sender As Object, ByVal e As EventArgs)
Dim semester As String = txtSemester.Text.Trim()
Dim spName As String = "GetStudentMarks"
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(constr)
Using cmd As SqlCommand = New SqlCommand(spName, con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@Semester", semester)
Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
Using dt As DataTable = New DataTable()
sda.Fill(dt)
gvStudent.DataSource = dt
gvStudent.DataBind()
End Using
End Using
End Using
End Using
End Sub
Screenshot
