SQL Server 2008: many-to-many relationship: Concatenation in SELECT query

Question

There are 3 tables:

1. Project
2. Tool
3. LinkProjectTool

I need a query that lists everything in the Project table plus an extra column called ProjectTools. This column should contain a comma delimited string with all the tool names belonging to each project.

The data is:

Table Project:

ID  Name        Client
------------------------
0   table       Anna
1   chair       Bobby
2   workbench   James
3   window      Jenny
4   shelves     Matthew

Table Tool:

ID  Name
------------------------
0   hammer
1   measuring tape
2   pliers
3   scissors
4   spanner
5   saw
6   screwdriver

Table LinkProjectTool:

IDProject   IDTool
-------------------
0       0
0       3
2       1
2       4
2       5

The result should be:

ID  Name        Client      ProjectTools
-------------------------------------------------------------
0   table       Anna        hammer, scissors
1   chair       Bobby
2   workbench   James       measuring tape, spanner, saw
3   window      Jenny
4   shelves     Matthew

Here are the queries I used to create these tables:

CREATE TABLE [dbo].[Project]
(
    [ID] [int] NOT NULL,
    [Name] [nvarchar](15) NOT NULL,
    [Client] [nvarchar](15) NULL
)

INSERT INTO [dbo].[Project]
       (ID, Name, Client)
     VALUES
       (0, 'table', 'Anna'),
       (1, 'chair', 'Bobby'),
       (2, 'workbench', 'James'),
       (3, 'window', 'Jenny'),
       (4, 'shelves', 'Matthew')

CREATE TABLE [dbo].[Tool](
    [ID] [tinyint] IDENTITY(0,1) NOT NULL,
    [Name] [nvarchar](30) NULL,
 CONSTRAINT [PK_Tool] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO [dbo].Tool
       (Name)
     VALUES
       ('hammer'),
       ('measuring tape'),
       ('pliers'),
       ('scissors'),
       ('spanner'),
       ('saw'),
       ('screwdriver')

CREATE TABLE [dbo].LinkProjectTool
(
    [IDProject] [int] NOT NULL,
    [IDTool] [tinyint] NULL
)

INSERT INTO [dbo].LinkProjectTool
    (IDProject, IDTool)
     VALUES
       (0, 0),
       (0, 3),
       (2, 1),
       (2, 4),
       (2, 5)

Could you, please, help?

Thank you.

Answer 1

You can use STUFF function alongside with FOR XML (see this answer for a more detailed explanation on how they work).

Assuming you want the project tools to be separated by a comma and a blank space, you can use the following query:

SELECT DISTINCT p.ID, p.Name, p.Client,
    ProjectTools = STUFF((
        SELECT ', ' + t.Name
        FROM Tool t
        WHERE t.ID IN (SELECT IDTool FROM LinkProjectTool WHERE IdProject = p.ID)
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
FROM Project p LEFT OUTER JOIN LinkProjectTool lpt ON p.Id = lpt.IDProject
ORDER BY p.ID