SQL JOIN по полю строки с разделителем

Question

Есть две таблицы. Для примера я создал ситуацию мебели и материала. В первой таблице название мебели и ид материала который используется. Во второй таблице ид и название материала. Нужно приджоинить к первой таблице вторую. Сложность в том, что может использоваться два типа материала и нужно как-то делать проверку: 1 или больше типов материалов. И если больше 1, то нужно строку делить на отдельные ИДшки и по каждой подтягивать значение и потом через запятую так же вывести. sql 12й, сплит не работает.

IF OBJECT_ID('tempdb..#111') IS NOT NULL DROP TABLE #111
CREATE TABLE #111
(col1 BIGINT,
name1 NVARCHAR (100),
name11 NVARCHAR (100))

IF OBJECT_ID('tempdb..#222') IS NOT NULL DROP TABLE #222
CREATE TABLE #222
(col2 BIGINT,
name2 NVARCHAR (100),
name22 NVARCHAR (100))

insert into #111 (col1, name1, name11 ) Values (1, 'Стульчик',  '1');
insert into #111 (col1, name1, name11 ) Values (2, 'Стульчик 2','2');
insert into #111 (col1, name1, name11 ) Values (3, 'Стол',      '1');
insert into #111 (col1, name1, name11 ) Values (4, 'Диван',     '3');
insert into #111 (col1, name1, name11 ) Values (5, 'Стол 2',    '4');

insert into #222 (col2, name2, name22 ) Values (1, 'Дерево','есть');
insert into #222 (col2, name2, name22 ) Values (2, 'Метал','есть');
insert into #222 (col2, name2, name22 ) Values (3, 'Замша','нет');
insert into #222 (col2, name2, name22 ) Values (4, 'Стекло','нет');


select col1 as 'номер', name1 as 'Тип', name11, t.name2 as 'материал'  
from #111 as o
left join #222 as t on o.name11 = t.col2

Но если предмет будет хранить два ид материала или три через запятую:

insert into #111 (col1, name1, name11 ) Values (3, 'Стол', '1, 3, 4');

Уже джоин не отработает. А нужно получить результат:

номер   Тип   name11    материал
3       Стол  1,3,4     Дерево, Замша, Стекло.

Посмотрел на такую штуку:

SELECT col1, name1, (case when name11 LIKE '%,%' then 0 else name11 end) as status from #111

Только после then нужно придумать логику котороя будет разбивать строку на элементы, вытаскивать по каждому элеменету, сбивать в одну строку и выводить. Если это возможно.

Answer 1

SELECT #111.col1 [номер], 
       #111.name1 [тип], 
       STRING_AGG(#333.value, ',') WITHIN GROUP ( ORDER BY #333.value ) name11, 
       STRING_AGG(#222.name2, ',') WITHIN GROUP ( ORDER BY #333.value ) [материал]
FROM #111
CROSS APPLY STRING_SPLIT(#111.name11, ',') #333
JOIN #222 ON #333.value = #222.col2
GROUP BY #111.col1, #111.name1

fiddle

Answer 2

Кажется, что ваш SQL Server 2008. Выявить это очень легко:

SELECT @@VERSION;

Ваша модель данных очень сомнительна. Я изменил ее, чтобы она была правильной. Вот ваше решение. Оно будет работать на SQL Server, начиная с 2008 года и позже.

SQL

-- DDL and sample data population, start
DECLARE @furniture TABLE (fur_id INT, furniture NVARCHAR (100));
DECLARE @material TABLE (mat_id INT, material NVARCHAR (100));
DECLARE @furniture_material TABLE (fur_id INT NOT NULL, mat_id INT NOT NULL);

INSERT INTO @furniture (fur_id, furniture) Values 
(1, N'Стульчик')
,(2, N'Стол');

INSERT INTO @material (mat_id, material ) Values 
(1, N'Дерево')
, (2, N'Метал')
, (3, N'Замша')
, (4, N'Стекло');

INSERT INTO @furniture_material (fur_id, mat_id) VALUES
(1, 1),
(1, 3),
(2, 1),
(2, 2),
(2, 4);
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = ',';

SELECT f.* 
    , STUFF((SELECT @separator + CAST(material AS NVARCHAR(100)) AS [text()]
             FROM @furniture_material AS fm
             INNER JOIN @material AS m ON m.mat_id = fm.mat_id
             WHERE fm.fur_id = f.fur_id 
             FOR XML PATH('')), 1, 1, NULL) AS MaterialList
FROM @furniture AS f;

Результат

+--------+-----------+---------------------+
| fur_id | furniture |    MaterialList     |
+--------+-----------+---------------------+
|      1 | Стульчик  | Дерево,Замша        |
|      2 | Стол      | Дерево,Метал,Стекло |
+--------+-----------+---------------------+

БЛОГ НА HUSL

SQL JOIN по полю строки с разделителем

Ответы (2 шт):