How to concatenate / present this data
-
(
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.
-
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.
If you install the SQLCLR Aggregates from http://groupconcat.codeplex.com you can then do something like this:
WITH cteAS (
SELECT Project + ' | ' + Task + ' | ' AS prefix,
RoleName + ' (' + dbo.GROUP_CONCAT(UserName) + ')' AS role_users
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
GROUP BY Project,
Task,
RoleName
)
SELECT cte.prefix + dbo.GROUP_CONCAT_D(cte.role_users, ' ~ ') AS roles_users
FROM cte
GROUP BY cte.prefix
ORDER BY cte.prefix;
To get this result:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Thanks for your reply and help. I need to talk my client into allowing the install.
-
You can achieve the same result using FOR XML but I prefer the way you can write the query using the GROUP_CONCAT approach. I am biased though, I wrote the SQLCLR objects up on CodePlex too. Let me know if your customer objects and I'll supply the XML equivalent.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply