T-SQL-将以逗号分隔的列转换为多列 [英] T-SQL -- convert comma-delimited column into multiple columns

问题描述

如何从下表中将 Values 列转换为多个列，并用当前用逗号分隔的单个值填充？转换前：

From the table below, how can I convert the Values column into multiple columns, populated with individual values that are currently separated by commas? Before the conversion:

Name  Values 
----  ------
John  val,val2,val3 
Peter val5,val7,val9,val14 
Lesli val8,val34,val36,val65,val71,val 
Amy   val3,val5,val99

转换结果应类似于：

Name  Col1  Col2  Col3  Col4  Col5  Col6 
----  ----  ----  ----  ----  ----  ----
John  val   val2  val3 
Peter val5  val7  val9  val14 
Lesli val8  val34 val36 val65 val71 val 
Amy   val3  val5  val99

推荐答案

首先，您使用什么数据库产品和版本？如果您使用的是SQL Server 2005及更高版本，则可以编写如下的Split用户定义函数：

First, what database product and version are you using? If you are using SQL Server 2005 and later, you can write a Split user-defined function like so:

CREATE FUNCTION [dbo].[Split]
(   
    @DelimitedList nvarchar(max)
    , @Delimiter varchar(2) = ','
)
RETURNS TABLE 
AS
RETURN 
    (
    With CorrectedList As
        (
        Select Case When Left(@DelimitedList, DataLength(@Delimiter)) <> @Delimiter Then @Delimiter Else '' End
            + @DelimitedList
            + Case When Right(@DelimitedList, DataLength(@Delimiter)) <> @Delimiter Then @Delimiter Else '' End
            As List
            , DataLength(@Delimiter) As DelimiterLen
        )
        , Numbers As 
        (
        Select TOP (Coalesce(Len(@DelimitedList),1)) Row_Number() Over ( Order By c1.object_id ) As Value
        From sys.objects As c1
            Cross Join sys.columns As c2
        )
    Select CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen As Position
        , Substring (
                    CL.List
                    , CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen     
                    , CharIndex(@Delimiter, CL.list, N.Value + 1)                           
                        - ( CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen ) 
                    ) As Value
    From CorrectedList As CL
        Cross Join Numbers As N
    Where N.Value < Len(CL.List)
        And Substring(CL.List, N.Value, CL.DelimiterLen) = @Delimiter
    )

然后，您可以使用类似于以下内容的方法将所需的值分开：

You can then split out the values in you want using something akin to:

Select Name, Values
From Table1 As T1
Where Exists    (
                Select 1
                From Table2 As T2
                    Cross Apply dbo.Split (T1.Values, ',') As T1Values
                    Cross Apply dbo.Split (T2.Values, ',') As T2Values
                Where T2.Values.Value = T1Values.Value
                    And T1.Name = T2.Name
                )

这篇关于T-SQL-将以逗号分隔的列转换为多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！