How SQL View can be used in this scenario

Question

Hi Guys,

I am using Datareader in my C# code to get data from db and dumping it into excel sheets. I am able to do it for everyrow correctly (based on the data from the sql view).

I have a problem in one of the sql views.

rolename roledesc roletype roleuser

Team Leader (required) xyzxyz Primary Mike Team Leader (required) xyzxyz Backup Cary Process Focal abcabc Primary Jeff Process Focal abcabc Backup Dave

As, when i read each row of this using reader i get, (Team Leader (required) xyzxyz Primary Mike) AND (Team Leader (required) xyzxyz Backup Cary )) in 2 separate rows.

But when i have to dump this data to excel sheet, i have to only dump the Team Leader (required) once and the roleuser should be Mike and next to him Cary.

I mean,

cell1 cell2 cell3 cell4

Team Leader (required) xyzxyz Mike Cary Process Focal abcabc Jeff Dave

Please help me in modifying the sql view for this.

Tables: rolename, roledesc comes from roledescription table, roletype comes from roletypedescription table, roleuser from username table,

I can explain if the above statements are not clear :)

Thanks Ramm

Answer 1

Hi, This is my SQL Query

SELECT dbo.tblRoleDescription.txtRoleName, dbo.tblRoleDescription.txtRoleDescription, dbo.tblRoleType.txtRoleTypeDescription, dbo.tblUserInfo.txtUserName, dbo.tblRoleAssignment.idSubProject, dbo.tblRoleAssignment.idLaunch FROM dbo.tblLaunchInfo INNER JOIN dbo.tblRoleAssignment ON dbo.tblLaunchInfo.idLaunch = dbo.tblRoleAssignment.idLaunch INNER JOIN dbo.tblProjectUsers ON dbo.tblRoleAssignment.idSubProject = dbo.tblProjectUsers.idSubProject AND dbo.tblRoleAssignment.idUser = dbo.tblProjectUsers.idUser INNER JOIN dbo.tblUserInfo ON dbo.tblProjectUsers.idUser = dbo.tblUserInfo.idUser INNER JOIN dbo.tblRoleDescription ON dbo.tblRoleAssignment.idRole = dbo.tblRoleDescription.idRole INNER JOIN dbo.tblRoleType ON dbo.tblRoleAssignment.idRoleType = dbo.tblRoleType.idRoleType

tblroleassignment -> idSubProject , idUser , idRoleType, idRole , idLaunch tblroledescription -> idRole(PK), txtRoleName, txtRoleDescription tblRoleType -> idRoleType (PK) , txtRoleTypeDescription tbluserinfo -> iduser, txtusername tbllaunchinfo -> idlaunch tblprojusers -> idsubproject, iduser

the sql query and the tables are given above, Please help me

Thanks Ramm