SQL Server 2008-将多值列拆分为具有唯一值的行 [英] SQL Server 2008 - split multi-value column into rows with unique values
问题描述
在SQL Server 2008数据库中,我有一列包含多个用分号分隔的值.一些值包含冒号.样本数据:
In a SQL Server 2008 database, I have a column with multiple values separated by semi-colons. Some values contain colons. Sample data:
key:value;key2:value;blah;foo;bar;A sample value:whee;others
key:value;blah;bar;others
A sample value:whee
我想从单独的行的每一行中获取所有唯一值:
I want to get all the unique values from each row in separate rows:
key:value
key2:value
blah
foo
bar
A sample value:whee
others
我看过各种split
函数,但是它们似乎都处理硬编码的字符串,而不是表中来自列的字符串.我该怎么办?
I've looked at various split
functions, but they all seem to deal with hard-coded strings, not strings coming from a column in a table. How can I do this?
编辑:托马斯的答案正确了!这是我的最终查询:
Thomas' answer got it! Here was my final query:
With SampleInputs As
(
select distinct myColumn from [myDatabase].[dbo].myTable where myColumn != ''
)
, XmlCte As
(
Select Cast( '<z>' + Replace( myColumn, ';', '</z><z>' ) + '</z>' As xml ) As XmlValue
From SampleInputs As I
)
Select Distinct Y.z.value('.','nvarchar(max)') As Value
From XmlCte
Cross Apply XmlValue.nodes('//z') Y(z)
我猜想XmlValue.nodes
和Y.z.value
的东西是魔术. O_o
I'm guessing the XmlValue.nodes
and Y.z.value
stuff is magic. O_o
推荐答案
With SampleInputs As
(
Select 'key:value;key2:value;blah;foo;bar;A sample value:whee;others' As [Data]
Union All Select 'key:value;blah;bar;others'
Union All Select 'A sample value:whee'
)
, XmlCte As
(
Select Cast( '<z>' + Replace( I.[Data], ';', '</z><z>' ) + '</z>' As xml ) As XmlValue
From SampleInputs As I
)
Select Distinct Y.z.value('.','nvarchar(max)') As Value
From XmlCte
Cross Apply XmlValue.nodes('//z') Y(z)
更新
以下是处理实体的上述版本:
Here's a version of the above that handles entities:
With SampleInputs As
(
Select 'key:value;key2:value;blah;foo;bar;A sample value:whee;others' As [Data]
Union All Select 'key:value;blah;bar;others'
Union All Select 'A sample value:whee'
Union All Select 'A sample value:<Oops>'
)
, XmlGoo As
(
Select Cast(
Replace(
Replace( Cast( Z.XmlValue As nvarchar(max) ), '{{', '<z>' )
, '}}', '</z>')
As Xml ) As Xmlvalue
From (
Select Cast(
(
Select '{{' + Replace( [Data], ';', '}}{{' ) + '}}'
From SampleInputs
For Xml Path(''), type
) As Xml ) As XmlValue
) As Z
)
Select Distinct Z.data.value('.', 'nvarchar(max)')
From XmlGoo
Cross Apply XmlValue.nodes('/z') Z(data)
这篇关于SQL Server 2008-将多值列拆分为具有唯一值的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!