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屋！