sku370870 (2/11/2016)

---

CREATE TABLE #tblProjects

(

ProjectID int,

Project varchar(50)

)

GO

INSERT INTO #tblProjects (ProjectID, Project)

SELECT 1, 'Project one' UNION ALL

SELECT 2, 'Project two'

----------------------------------

CREATE TABLE #tblTasks

(

TaskID int,

Task varchar(50)

)

GO

INSERT INTO #tblTasks (TaskID, Task)

SELECT 1, 'Task one' UNION ALL

SELECT 2, 'Task two' UNION ALL

SELECT 3, 'Task three' UNION ALL

SELECT 4, 'Task four'

----------------------------------

CREATE TABLE #tblRoles

(

RoleID int,

RoleName varchar(50)

)

GO

INSERT INTO #tblRoles (RoleID, RoleName)

SELECT 1, 'Project Manager' UNION ALL

SELECT 2, 'Administrator' UNION ALL

SELECT 3, 'Coordinator'

----------------------------------

CREATE TABLE #tblUsers

(

UserID int,

UserName varchar(50)

)

GO

INSERT INTO #tblUsers (UserID, UserName)

SELECT 1, 'Fred' UNION ALL

SELECT 2, 'Barney' UNION ALL

SELECT 3, 'Betty'

----------------------------------

CREATE TABLE #tblTaskRoles

(

TaskRoleID int,

TaskID int,

RoleID int

)

GO

INSERT INTO #tblTaskRoles (TaskRoleID, TaskID, RoleID)

SELECT 1, 1, 1 UNION ALL

SELECT 2, 1, 2 UNION ALL

SELECT 3, 2, 2 UNION ALL

SELECT 4, 2, 3 UNION ALL

SELECT 5, 3, 1 UNION ALL

SELECT 6, 4, 3

----------------------------------

CREATE TABLE #tblProjectsTasks

(

ProjectTaskID int,

ProjectID int,

TaskID int,

)

GO

INSERT INTO #tblProjectsTasks (ProjectTaskID, ProjectID, TaskID)

SELECT 1, 1, 1 UNION ALL

SELECT 2, 1, 2 UNION ALL

SELECT 3, 1, 3 UNION ALL

SELECT 4, 1, 4 UNION ALL

SELECT 5, 2, 1 UNION ALL

SELECT 6, 2, 2 UNION ALL

SELECT 7, 2, 3 UNION ALL

SELECT 8, 2, 4

----------------------------------

CREATE TABLE #tblProjectsRoleUsers --this is who is assigned to which role on this project

(

ProjectRoleUserID int,

ProjectID int,

RoleID int,

UserID int

)

INSERT INTO #tblProjectsRoleUsers(ProjectRoleUserID, ProjectID, RoleID, UserID)

SELECT 1, 1, 1, 1 UNION ALL

SELECT 2, 1, 1, 2 UNION ALL

SELECT 3, 1, 2, 1 UNION ALL

SELECT 4, 1, 2, 3 UNION ALL

SELECT 5, 1, 3, 2 UNION ALL

SELECT 6, 1, 3, 3

SELECT Project, Task, RoleName, UserName FROM #tblProjects

INNER JOIN #tblProjectsTasks ON #tblProjects.ProjectID = #tblProjectsTasks.ProjectID

INNER JOIN #tblTasks ON #tblProjectsTasks.TaskID = #tblTasks.TaskID

INNER JOIN #tblTaskRoles ON #tblTasks.TaskID = #tblTaskRoles.TaskID

INNER JOIN #tblRoles ON #tblTaskRoles.RoleID = #tblRoles.RoleID

INNER JOIN #tblProjectsRoleUsers ON #tblProjects.ProjectID = #tblProjectsRoleUsers.ProjectID AND #tblRoles.RoleID = #tblProjectsRoleUsers.RoleID

INNER JOIN #tblUsers ON #tblProjectsRoleUsers.UserID = #tblUsers.UserID

ORDER BY Project, Task, RoleName, UserName

DROP TABLE #tblProjects

DROP TABLE #tblTasks

DROP TABLE #tblRoles

DROP TABLE #tblUsers

DROP TABLE #tblTaskRoles

DROP TABLE #tblProjectsTasks

DROP TABLE #tblProjectsRoleUsers

A list of projects, tasks, roles and users. Any project can have any task. Any task can have one or more roles assigned to it. On a project, any user can be assigned to any role. The data above displays as a list of tasks on a project, which roles have to do the task and which users are assigned to the role on that project.

But, I need to get the data displayed in a sort of report form - so that all the info about a task is on one line. Using | as a column separator and a tilde to show where I need to return a html return ... the first couple of lines I need to return would be:

Project one | Task four | Coordinator (Barney, Betty)

Project one | Task one | Administrator (Betty, Fred) ~ Project Manager (Barney, Fred)

I already do something similar - but using UDFs (with coalesce to concatenate values) to return the roles for each task - and likewise for the users for each task (but you can't connect the user to the role). At the moment I can return ...

Project one | Task four | Coordinator | Barney, Betty

Project one | Task one | Administrator, Project Manager | Betty, Fred, Barney, Fred

I can achieve what I need doing something awful like putting a cursor into my UDFS - looping through the roles and building a string with the users for each role etc.

Just wondered if anyone on here can tell me how to do this without a cursor in the UDFS that currently use coalesce to return the roles and users for each task.