How to format the result set of parent child records?

Question

All,

I have a result set coming from several SQL Server tables, and I need to know what the best way to get the sorted records I need would be.

I have the following records; a user name and module number as parent records, and then for each of these modules there are multiple sub-module child records. The problem I'm having is that the module record has an association with each sub-module record. Therefore I get the following records back from my stored procedure:

NAME            Module  Sub-module
Derek Kenney    1       1
Derek Kenney    1       2
Derek Kenney    1       3
Derek Kenney    1       4
Derek Kenney    1       5

What I want is:

NAME            Module  Sub-module
Derek Kenney    1       1
                        2
                        3
                        4
                        5

I think my problem is within 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 within ADO.NET to get the result set I want and I'm not aware of it. Any help would be appreciated.

Derek Kenney

Answer 1

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 the case to show the YourName when the Module changes