如何动态关联含多值字段的两张SQL表并得到指定结果？

表结构与数据 #

表#A #

CREATE TABLE #A
(
    NAME VARCHAR(10),
    NUMBER INT,
    ID VARCHAR(5)
)

INSERT INTO #A VALUES ('JOHN', 20, 'J10'), ('MIKE', 2, 'M5')

表#A数据：

表#B #

CREATE TABLE #B
(
    PRODUCT VARCHAR(10),
    NAME VARCHAR(20)
)

INSERT INTO #B 
VALUES ('APPLE', 'JOHN, HARRY'),
       ('ORANGE', 'JOHN,MIKE'),
       ('MANGO', 'JOHN, KATTY, ALEX')

表#B数据：

解决方案 #

利用SQL Server的STRING_SPLIT函数拆分#B中NAME字段的逗号分隔值，同时处理值中的前后空格，再与#A进行关联：

SELECT 
    a.NAME,
    a.NUMBER,
    a.ID,
    b.PRODUCT
FROM #A a
JOIN (
    SELECT 
        PRODUCT,
        LTRIM(RTRIM(value)) AS NAME
    FROM #B
    CROSS APPLY STRING_SPLIT(NAME, ',')
) b ON a.NAME = b.NAME
ORDER BY a.NAME, b.PRODUCT

执行结果 #

内容的提问来源于stack exchange，提问作者Kjoshi