How do I format the resulting set of parent child records?
All,
I have a result set coming from multiple SQL Server tables and I need to know what the best way to get the sorted records that I need would be.
I have the following entries; username and module number as parent entries, and then there are multiple submodule child entries for each of these modules. The problem I'm running into is that a module entry has an association with every submodule entry. Therefore, from my stored procedure, I am returning the following records:
NAME Module Sub-module
Derek Kenney 1 1
Derek Kenney 1 2
Derek Kenney 1 3
Derek Kenney 1 4
Derek Kenney 1 5
I want to:
NAME Module Sub-module
Derek Kenney 1 1
2
3
4
5
I think my problem is in my sproc which I posted below.
ALTER PROCEDURE [dbo].[uspGetReportData]
AS
BEGIN
SELECT first_name, last_name, email, country_id, T_User_Info.user_id, T_Modules_Viewed.module_id as 'modules viewed', T_Modules_Completed.module_id as 'modules completed'
FROM T_User_Info inner join T_Modules_Viewed on T_User_Info.user_id = T_Modules_Viewed.user_id left outer join T_Modules_Completed
on T_Modules_Viewed.user_id = T_Modules_Completed.user_id
END
Perhaps there is a way in ADO.NET to get the result set I want and I am not aware of it. Any help would be appreciated.
Derek Kenny
source to share
try something like this:
DECLARE @YourData table (YourName varchar(20), Module char(1), SubModule char(1))
INSERT INTO @YourData VALUES ('Derek Kenney',1,1)
INSERT INTO @YourData VALUES ('Derek Kenney',1,2)
INSERT INTO @YourData VALUES ('Derek Kenney',1,3)
INSERT INTO @YourData VALUES ('Derek Kenney',1,4)
INSERT INTO @YourData VALUES ('Derek Kenney',1,5)
INSERT INTO @YourData VALUES ('Derek Kenney',2,1)
INSERT INTO @YourData VALUES ('Derek Kenney',2,2)
INSERT INTO @YourData VALUES ('Derek Kenney',2,3)
INSERT INTO @YourData VALUES ('Joe Smith' ,1,1)
INSERT INTO @YourData VALUES ('Joe Smith' ,2,1)
INSERT INTO @YourData VALUES ('Joe Smith' ,2,2)
SELECT
CASE WHEN NameRank=1 /*OR NameModuleRank=1*/ THEN YourName ELSE '' END AS YourName
,CASE NameModuleRank WHEN 1 THEN Module ELSE '' END AS Module
,SubModule
FROM (SELECT
YourName,Module,SubModule
,ROW_NUMBER() OVER(partition by YourName ORDER BY YourName) AS NameRank
,ROW_NUMBER() OVER(partition by YourName,Module ORDER BY YourName,Module) AS NameModuleRank
FROM @YourData
) dt
OUTPUT:
YourName Module SubModule
-------------------- ------ ---------
Derek Kenney 1 1
2
3
4
5
2 1
2
3
Joe Smith 1 1
2 1
2
(11 row(s) affected)
if you want you can uncomment the OR in this case to show your name when the module changes
source to share