首先，您需要将数据分成两列，如

SELECT CASE
     WHEN name LIKE '%,%' THEN LEFT(name, Charindex(' ', products) - 1)
     ELSE name
   END,
   CASE
     WHEN name LIKE '%,%' THEN RIGHT(name, Charindex(' ', Reverse(products)) - 1)
   END
   FROM   YourTable

然后你需要把这个和同一个表合并。。。最后的代码看起来像。。。

select count( distinct abc), abc from 
    (
    SELECT CASE
       WHEN PA_NAME LIKE '% %' THEN LEFT(PA_NAME, Charindex(' ', PA_NAME) - 1)
       ELSE PA_NAME
    END [abc]
    FROM   phparty

    union all

    SELECT CASE
     WHEN PA_NAME LIKE '% %' THEN RIGHT(PA_NAME, Charindex(' ', Reverse(PA_NAME)) -1)
   END [abc]
    FROM   phparty
    ) t group by abc

在这里，您可以用您的\u列\u名称替换您的\u名称

您正在sql表中存储csv数据，这不是一件好事。但看起来你正试图摆脱这一点，这是一件好事。这里有一个选项，使用 SUBSTRING_INDEX :

SELECT Products, COUNT(*) AS count
FROM
(
    SELECT SUBSTRING_INDEX(Products, ',', 1) AS Products FROM yourTable
    UNION ALL
    SELECT SUBSTRING_INDEX(Products, ',', -1) FROM yourTable
) t
GROUP BY Products
ORDER BY
    count DESC, Products;

演示