score:1

As Stephen Muecke said in the comment section that Projects and Clients are 2 different things. You should have a Client table and a separate Project table containing a column with foreign key to the Client table. So below solution is according to your existing table structure.

Dummy Schema

DECLARE @clientprj TABLE ([id] int identity(1,1), [name] varchar(30), Desc1 varchar(30), ParentId int, pType varchar(30));

INSERT @clientprj ([name], Desc1, ParentId, pType) VALUES ('client1', '', 0, 'clt'), ('prj1', '', 1, 'prj'), ('prj2', '', 1, 'prj'), ('client2', '', 0, 'clt'), ('prj n', '', 4, 'prj')

and Here is the query

SELECT GroupName, GroupKey, ProjName, ProjId
FROM 
(
    (
        SELECT NAME AS GroupName
            ,Id AS GroupKey
        FROM @clientprj m
        WHERE ParentId = 0
        ) m 
    FULL OUTER JOIN 
    (
        SELECT NAME AS ProjName
            ,Id AS ProjId
            ,ParentId
        FROM @clientprj
    ) 
    t ON m.GroupKey = t.ParentId
)
WHERE ParentId <> 0

Which return the following output.

GroupName   GroupKey    ProjName    ProjId
client1       1           prj1           2
client1       1           prj2           3
client2       4           prj n          5

and your controller method which calls this query like this - 

Model.ddlProject = db.NewMethod
                    .Select(t => new GroupedSelectListItem
                     {
                       GroupName = t.GroupName,
                       GroupKey = t.GroupKey.ToString(),
                       Text = t.ProjName,
                       Value = t.ProjId.ToString()
                      });

and then bind your dropdownlist. Best of luck...


Related Articles