Odtnhj

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.

You can use STUFF function.

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