将逗号分隔的列值转换为行

Question

我有一张名为Sample的表格，其中存储着以下数据

Id   | String
--------------
1     abc,def,ghi
2     jkl,mno,pqr

我需要像这样的输出..

Id   | processedrows
--------------
1     abc
1     def
1     ghi
2     jkl
2     mno
2     pqr

我该如何在SQL Server中使用select查询来实现相同的操作？

- mhn

---

“SQL”指的是结构化查询语言，是许多数据库系统使用的一种语言，但并不是一个数据库产品...很多东西都是供应商特定的，因此我们需要了解您正在使用哪个数据库系统（以及其版本）... - marc_s

首先，你使用了一个糟糕的数据模型。逗号分隔字符串几乎永远不应该出现在数据库中。句号。 - user554546

在人们愿意提供帮助的同时，请先搜索档案。拆分CSV字符串是一个非常常见的问题。尽管我同意Jack Maney的观点，如果可能的话 - 你应该更改你的数据模型。存储CSV字符串会引发麻烦。 - Leigh

从来就不是永远。我完全同意存储逗号分隔列表是一个麻烦的做法，但非规范化有其应用场景。话虽如此，将列表存储在单个列中然后使用SQL单独访问列表元素肯定会很棘手。请参阅https://dev59.com/gHA65IYBdhLWcg3wvxaE#3653574 - Bill Karwin

为什么有那么多评论告诉这个人他的数据模型是“错误”的？根据我的经验，你并不总是能够决定数据模型，或者也许这个模型对他来说很有效。无论是他在处理别人的错误还是他得到了自己想要的数据模型。 - FistOfFury

Answer 1

试试这个

 SELECT A.[id],  
     Split.a.value('.', 'VARCHAR(100)') AS String  
 FROM  (SELECT [id],  
         CAST ('<M>' + REPLACE([string], ',', '</M><M>') + '</M>' AS XML) AS String  
     FROM  TableA) AS A CROSS APPLY String.nodes ('/M') AS Split(a); 

请查看这里

http://www.sqljason.com/2010/05/converting-single-comma-separated-row.html

- SRIRAM

---

谢谢！它帮助了我！ - Gidil

太棒了！节省了很多时间！ - Shahdat

不错的答案，但对于特殊字符或多语言需求，需要将Split.a.value（'。'，'VARCHAR（100）'）更改为Split.a.value（'。'，'NVARCHAR（100）'）。 - GMD

Answer 2

新的做法是这样的：

    SELECT
         a.Id,
         b.value
    FROM
         Sample a 
         cross apply string_split(a.string,',') b

- Sandeep Sirohi

Answer 3

SELECT EmployeeID,
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Certs
FROM
(
SELECT EmployeeID,CAST('<XMLRoot><RowData>' + REPLACE(Certs,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM   @t
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)

- Sadacharan SS

---

我不确定这是否回答了问题，但似乎并没有；该问题没有名为“EmployeeID”的字段，并且将字符串值转换为XML对我来说似乎过于复杂。 - DaveyDaveDave

虽然这段代码片段很受欢迎，可能会提供一些帮助，但如果它包括了解释如何解决问题的说明，那将会大有改进。没有这个，你的答案就会少很多教育价值 - 记住，你正在回答未来读者的问题，而不仅仅是现在提问的人！请编辑你的答案添加解释，并指出适用的限制和假设。 - Toby Speight

Answer 4

试试这个。你会得到你的输出。

SELECT id,
PARSENAME(REPLACE(Split.a.value('.', 'VARCHAR(100)'),'-','.'),1) 'Values' 
FROM  
(
     SELECT algorithms,
     CAST ('<M>' + REPLACE(string, ',', '</M><M>') + '</M>' AS XML) AS Data 
     FROM   <TableName> 
) AS A 
CROSS APPLY Data.nodes ('/M') AS Split(a)

- Phani

Answer 5

让我们尝试下面的脚本：

declare @str varchar(max)

SELECT @str = isnull(@str +',', '') + a.Value
FROM (SELECT Value Entityvalue from Table) a

select @str

- RickyRam