从 sql server 表中拆分逗号分隔值 [英] split comma seprate value from table in sql server

问题描述

我有一个SQL表，其中有很多记录，我想知道其中有多少个名字以及一个名字在其中的时间.

i have one SQL table in which many records, i want to know how many names are in it and how much time one name in it.

表名Mst

Name

john,smith,alax,rock
smith,alax,sira
john,rock
rock,sira

我想知道有多少名字以及它的数量.

I want to find how much name are there and count of its.

预期的输出应该是这样的

expected output should be like this

Name           Count

john           2
smith          2
alax           2
rock           3
sira           2

帮我解决.

推荐答案

SELECT y.Name, count(*) Count
FROM
(VALUES
('john,smith,alax,rock'),
('smith,alax,sira'),
('john,rock'),
('rock,sira')) x(names)
CROSS APPLY
(
SELECT t.c.value('.', 'VARCHAR(2000)') Name
     FROM (
         SELECT x = CAST('<t>' + 
               REPLACE(x.names, ',', '</t><t>') + '</t>' AS XML)
     ) a
     CROSS APPLY x.nodes('/t') t(c)
     ) y
GROUP BY y.Name

结果:

Name   Count
alax   2
john   2
rock   3
sira   2
smith  2

这篇关于从 sql server 表中拆分逗号分隔值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！