Парсинг столбца и разворот по его значениям

Question

Есть таблица конфет

Id    name      parameter
1   Конфета1    синяя, вкусная, блестящая
2   Конфета2    красивая, сладкая, синяя, вкусная

Есть справочник параметров

Id name
1  синяя
2  вкусная
3  блестящая
4  красивая
5  сладкая
6  красная

Мне нужно распарсить поле parameter и развернуть его в строку и получить id

Id    name   parameterId
1   Конфета1    1
2   Конфета1    2
3   Конфета1    3
4   Конфета2    4
5   Конфета2    5
6   Конфета2    1
7   Конфета2    3

Answer 1

Вот как это сделать в MS SQL Server.

SQL

-- DDL and sample data population, start
DECLARE @конфет TABLE (Id INT IDENTITY PRIMARY KEY, [name] NVARCHAR(20), parameterList NVARCHAR(100));
INSERT INTO @конфет (name, parameterList)
VALUES
(N'Конфета1', N'синяя, вкусная, блестящая'),
(N'Конфета2', N'красивая, сладкая, синяя, вкусная');

DECLARE @parameter TABLE (ID INT IDENTITY PRIMARY KEY, [name] NVARCHAR(20));
INSERT INTO @parameter ([name]) VALUES
(N'синяя'),
(N'вкусная'),
(N'блестящая'),
(N'красивая'),
(N'сладкая'),
(N'красная');
-- DDL and sample data population, end

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

;WITH rs AS
(
    SELECT *
        , CAST('<root><r>' + 
              REPLACE(parameterList, @separator, '</r><r>') + 
            '</r></root>' AS XML) AS xmldata
    FROM @конфет
)
SELECT rs.ID, rs.[name], p.ID
    , c.value('(./text())[1] cast as xs:token?', 'NVARCHAR(20)') AS parameter
FROM rs 
    CROSS APPLY xmldata.nodes('/root/r') AS t(c)
    INNER JOIN @parameter AS p
        ON c.value('(./text())[1] cast as xs:token?', 'NVARCHAR(20)') = p.[name];

Вывод

+----+----------+----+-----------+
| ID |   name   | ID | parameter |
+----+----------+----+-----------+
|  1 | Конфета1 |  1 | синяя     |
|  1 | Конфета1 |  2 | вкусная   |
|  1 | Конфета1 |  3 | блестящая |
|  2 | Конфета2 |  4 | красивая  |
|  2 | Конфета2 |  5 | сладкая   |
|  2 | Конфета2 |  1 | синяя     |
|  2 | Конфета2 |  2 | вкусная   |
+----+----------+----+-----------+

MS SQL Server 2016 и более поздние версии, STRING_SPLIT()

-- Method #2
-- by Novitskiy Denis
 SELECT * 
 FROM @конфет
    CROSS APPLY STRING_SPLIT(parameterList, @separator)
    INNER JOIN @parameter AS p ON LTRIM(value) = p.[name];

MS SQL Server 2016 и более поздние версии, Json

-- Method #3
SELECT * 
FROM @конфет
    CROSS APPLY (
          SELECT LTRIM(RTRIM([value])) AS [word]
          FROM OPENJSON('["' + REPLACE(parameterList, @separator, '","') + '"]')
        ) AS j
INNER JOIN @parameter AS p ON j.[word] = p.[name];

БЛОГ НА HUSL

Парсинг столбца и разворот по его значениям

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