How to group the data by student name and display data in multi level

Question

Post reply

How to group the data by student name and display data in multi level

sqldevlearn

Old Hand

Points: 397

February 18, 2022 at 12:52 pm

#3985379

I am having the data as follows

DECLARE @Student TABLE
(
StudentId INT,
StudentName NVARCHAR(200),
StudentRollNo NVARCHAR(20)
)

INSERT INTO @Student VALUES
(1, 'GDOVFKMIZS', 'S12345'),
(2, 'QXAKWOYMRZ', 'X1256')

DECLARE @StudentHistory TABLE
(
StudentId INT,
Remarks NVARCHAR(500),
StartDate DATETIME,
EndDate DATETIME
)

INSERT INTO @StudentHistory (StudentId, StartDate, EndDate, Remarks) VALUES
(1, '2021-01-01', '2021-03-28', 'Test Remarks'),
(1, '2022-01-01', NULL, 'Test Remarks, New Remarks'),
(2, '2022-01-01', NULL, 'New')

DECLARE @SubjectHistory TABLE
(
StudentId INT,
SubjectName NVARCHAR(100),
StartDate DATETIME,
EndDate DATETIME
)

INSERT INTO @SubjectHistory (StudentId, SubjectName, StartDate, EndDate) VALUES
(1, 'Social', '2021-01-01', '2021-03-28'),
(1, 'Science', '2022-01-01', NULL)

SELECT DISTINCT s.StudentName, sh.StartDate, sh.EndDate, s.StudentRollNo, sh.Remarks,
sbh.SubjectName, sbh.StartDate, sbh.EndDate
FROM @Student S INNER JOIN @StudentHistory sh on s.StudentId = sh.StudentId
LEFT JOIN @SubjectHistory sbh on sbh.StudentId = s.StudentId

I am trying to group the data by student so that it will display data as follows

testdata

Similarly I need to group other student data

This topic was modified 2 years, 9 months ago by sqldevlearn.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

kaj SSCommitted Points: 1605 More actions · Answer 1

Looks like you already have all the data connected. Now you only need to have the data presented in the right way. That task is for reporting tools or other presentation layer tools.

There are a number of those around. Microsoft alone offer at least two: SSRS (SQL Server Reporting Services) and Access.

This reply was modified 2 years, 9 months ago by kaj. Reason: typo
This reply was modified 2 years, 9 months ago by kaj.

kaj SSCommitted Points: 1605 More actions · Answer 2

kaj wrote:

Looks like you already have all the data connected. Now you only need to have the data presented in the right way. That task is for reporting tools or other presentation layer tools.
There are a number of those around. Microsoft alone offer at least two: SSRS (SQL Server Reporting Services) and Access.

Ohh right, you've posted your question in the SSRS group. I didn't notice that. Sorry about my glib comment then. :-||

sqldevlearn Old Hand Points: 397 More actions · Answer 3

Yeah I need help in rdl design as per the posted image

pietlinden SSC Guru Points: 63503 More actions · Answer 4

Looks like you can drop a rectangle onto your report canvas and then put the two textboxes from the parent and then a tablix based on the child table inside the same rectangle.

sqldevlearn Old Hand Points: 397 More actions · Answer 5

I tried but no luck here is the RDL attaching

USE [Demo]
GO
/****** Object:  StoredProcedure [dbo].[up_GetStudentHistory]    Script Date: 21-02-2022 15:39:43 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*****************************************************************************
**File: up_GetStudentHistory.sql
**Name: up_GetStudentHistory
**Desc: 
**
**Return values: 
** 
**Called by:   
**              
**Parameters:
**InputOutput
**     ---------------------
**Auth: Dorababu Meka
**Date: 11/06/2021
**
*******************************************************************************
**Change History
*******************************************************************************
*******************************************************************************/
ALTER PROCEDURE [dbo].[up_GetStudentHistory]

AS
SET NOCOUNT ON
BEGIN
DECLARE @Student TABLE
(
StudentId INT,
StudentName NVARCHAR(200),
AdmitDate DATETIME,
StudentRollNo NVARCHAR(20)
)

INSERT INTO @Student VALUES
(1, 'GDOVFKMIZS', '2021-01-01', 'S12345'),
(2, 'QXAKWOYMRZ','2021-02-02', 'X1256')

DECLARE @StudentHistory TABLE
(
StudentId INT,
Remarks NVARCHAR(500),
HistStartDate DATETIME,
HistEndDate DATETIME
)

INSERT INTO @StudentHistory (StudentId, HistStartDate, HistEndDate, Remarks) VALUES
(1, '2021-01-01', '2021-03-28', 'Test Remarks'),
(1, '2022-01-01', NULL, 'Test Remarks, New Remarks'),
(2, '2022-01-01', NULL, 'New')

DECLARE @SubjectHistory TABLE
(
StudentId INT,
SubjectName NVARCHAR(100),
SubjStartDate DATETIME,
SubjEndDate DATETIME
)

INSERT INTO @SubjectHistory (StudentId, SubjectName, SubjStartDate, SubjEndDate) VALUES
(1, 'Social', '2021-01-01', '2021-03-28'),
(1, 'Science', '2022-01-01', NULL)

SELECT DISTINCT s.StudentName, s.AdmitDate, sh.HistStartDate, sh.HistEndDate, s.StudentRollNo, sh.Remarks,
sbh.SubjectName, sbh.SubjStartDate, sbh.SubjEndDate
FROM @Student S INNER JOIN @StudentHistory sh on s.StudentId = sh.StudentId
LEFT JOIN @SubjectHistory sbh on sbh.StudentId = s.StudentId 



END
GRANT EXEC ON [dbo].up_GetStudentHistory TO PUBLIC

This reply was modified 2 years, 9 months ago by sqldevlearn.

Attachments:

You must be logged in to view attached files.

sqldevlearn Old Hand Points: 397 More actions · Answer 6

I have update a bit but I couldn't group it based on Student name

group

Attachments:

You must be logged in to view attached files.