sql - 将字符串拆分为多列TSQL

Question

我正在处理 TSQL 中的存储过程。select * from @tempcostings 在逗号分隔列表中给出阶段。由于阶段的数量是动态的，我如何将它们拆分为列。

我看到有各种使用 string_split 和 cross apply 的例子，但看不到如何应用它。下图显示了我的表格以及我想要实现的目标。

尝试这个会得到错误找不到列“Prod_Attributes”或用户定义的函数或聚合“Prod_Attributes.value”，或者名称不明确。

  ;WITH cte (PK, product,standardcost,currentcost,variance,stages)
AS
(
SELECT 
    [PK],
    [product],
    [standardcost],
    [currentcost],
    [variance],
    CONVERT(XML,'<Product><Attribute>' 
        + REPLACE([stages],',', '</Attribute><Attribute>') 
        + '</Attribute></Product>') AS Prod_Attributes
FROM @tempcostings2021
)
SELECT 
    [PK],
    [product],
    [standardcost],
    [currentcost],
    [variance],
    Prod_Attributes.value('/Product[1]/Attribute[1]','varchar(25)') AS [Stage1],
    Prod_Attributes.value('/Product[1]/Attribute[2]','varchar(25)') AS [Stage2],
    Prod_Attributes.value('/Product[1]/Attribute[3]','varchar(25)') AS [Stage3],
    Prod_Attributes.value('/Product[1]/Attribute[4]','varchar(25)') AS [Stage4]
FROM cte

标签： sqltsql

Answer 1

复杂但它可以处理并返回动态数量的 Stage 列（将 CarTableType 更改为您的表类型）：

ALTER PROCEDURE [dbo].[spInsertCars]
@tempcostings CarTableType READONLY
AS
BEGIN
    DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX), @StageCount as int, 
    @params nvarchar(4000) = '@tempcostings CarTableType READONLY';

    select @StageCount = max( LEN(stages) - LEN(REPLACE(stages, ',', '')) + 1) from @tempcostings

    ;with CTE as
    (
        select 1 Number
        union all
        select Number +1 from CTE where Number<@StageCount
    )
    select @cols = STRING_AGG( '[Stage' + convert(varchar, Number) + ']', ', ') from CTE

    set @query = 'SELECT [PK],[product],[standardcost],[currentcost],[variance], ' + @cols + ' from 
    (
        select [PK],[product],[standardcost],[currentcost],[variance], REPLACE(REPLACE(Value, ''['', ''''), '']'', '''') stage, ''Stage'' + convert(varchar, ROW_NUMBER() over (PARTITION BY [PK],[product],[standardcost],[currentcost],[variance] order by [PK])) StageName
        from
        @tempcostings
        CROSS APPLY STRING_SPLIT(stages, '','')
    ) x
    pivot 
    (
        min([stage])
        for [StageName] in (' + @cols + ')
    ) p'

    exec sp_executesql @query, @params, @tempcostings = @tempcostings;
END