SQL 将逗号分隔的列值转换为行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13873701/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert Comma Separated column value to rows
提问by mhn
I have a table Samplewith data stored like below
我有一个表 Sample，其中的数据存储如下
Id | String
--------------
1 abc,def,ghi
2 jkl,mno,pqr
I need the output like..
我需要像..的输出
Id | processedrows
--------------
1 abc
1 def
1 ghi
2 jkl
2 mno
2 pqr
How can I do the same with a select query in SQL Server?
如何对 SQL Server 中的选择查询执行相同操作？
回答by SRIRAM
try this
尝试这个
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);
refer here
参考这里
http://www.sqljason.com/2010/05/converting-single-comma-separated-row.html
http://www.sqljason.com/2010/05/converting-single-comma-separated-row.html
回答by Sadacharan SS
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)
回答by RickyRam
Let's try the below script:-
让我们试试下面的脚本：-
declare @str varchar(max)
SELECT @str = isnull(@str +',', '') + a.Value
FROM (SELECT Value Entityvalue from Table) a
select @str
回答by Phani
Try with this. You will get your output.
试试这个。你会得到你的输出。
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)
