SQL Server 2008: отношение "многие ко многим": конкатенация в запросе SELECT
Есть 3 таблицы:
- Проект
- Орудие труда
- LinkProjectTool
Мне нужен запрос, который перечисляет все в таблице Проект плюс дополнительный столбец с именем ProjectTools. Этот столбец должен содержать строку, разделенную запятыми, со всеми названиями инструментов, принадлежащих каждому проекту.
Данные:
Таблица Проект:
ID Name Client
------------------------
0 table Anna
1 chair Bobby
2 workbench James
3 window Jenny
4 shelves Matthew
Таблица Орудие труда:
ID Name
------------------------
0 hammer
1 measuring tape
2 pliers
3 scissors
4 spanner
5 saw
6 screwdriver
Таблица LinkProjectTool:
IDProject IDTool
-------------------
0 0
0 3
2 1
2 4
2 5
Результат должен быть:
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
Вот запросы, которые я использовал для создания этих таблиц:
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)
Не могли бы вы помочь?
Спасибо.
Ответы 1
Вы можете использовать функцию STUFF вместе с FOR XML (см. этот ответ для более подробного объяснения того, как они работают).
Предполагая, что вы хотите, чтобы инструменты проекта были разделены запятой и пробелом, вы можете использовать следующий запрос:
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
Хотя это нормальный ответ, он 1) дублируется и более важен: 2) Вы действительно должны понимать свой собственный код. STUFF() здесь не имеет ничего общего с групповое сжатие. Это обеспечивается подвыборкой с FOR XML PATH. Эта функция STUFF() не имеет другого смысла, кроме как отрезать ведущий запятая + пробел ...
Примеров масса. Но - глядя на ваш проработанный минимальный воспроизводимый пример - я предполагаю, что вы уже вложили довольно много времени. +1 с моей стороны за вопрос ...