SQL Server 2008 - 将多值列拆分为具有唯一值的行

Question

在 SQL Server 2008 数据库中，我有一列包含用分号分隔的多个值。某些值包含冒号。示例数据：

key:value;key2:value;blah;foo;bar;A sample value:whee;others
key:value;blah;bar;others
A sample value:whee

我想从单独的行中获取每行的所有唯一值：

key:value
key2:value
blah
foo
bar
A sample value:whee
others

我查看了各种 split 函数，但它们似乎都处理硬编码字符串，而不是来自表中的一列。我该怎么做？

编辑：托马斯的回答明白了！这是我的最终查询：

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 和 Yzvalue 的东西很神奇。奥奥

Answer 1

对于 split 函数，您可以使用交叉应用：

select distinct SS.part
from YourTable
  cross apply dbo.SplitString(YourColumn, ';') as SS

这里 SplitString 接受两个参数，即字符串列和分隔符，并有一个名为 part 的列，其中返回值。

Answer 2

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)

---

更新

这是上述处理实体的版本：

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)