CODEWITHSUNDEEP
sqlt-sql
Emma
Emma

Reputation: 562

Split string into multiple columns TSQL

i'm working on a stored procedure in TSQL. select * from @tempcostings gives stages in a comma seperated list. How can I split these into columns as the amount of stages is dynamic.

I see there are various examples using string_split and cross apply but cannot see how to apply this. Image below shows my table and what I am trying to achieve.

enter image description here

Trying this put getting the error Cannot find either column "Prod_Attributes" or the user-defined function or aggregate "Prod_Attributes.value", or the name is ambiguous.

  ;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

Upvotes: 1

Views: 340

Answers (2)

Kristian Fitzgerald
Kristian Fitzgerald

Reputation: 269

Complicated but it can deal with and return a dynamic number of Stage columns (changing CarTableType to your table type):

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

enter image description here

Upvotes: 3

persian-theme
persian-theme

Reputation: 6638

change query to :

;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],
    stages.query('/Product/Attribute[1]').value('/', 'varchar(max)') AS [Stage1],
    stages.query('/Product/Attribute[2]').value('/', 'varchar(max)') AS [Stage2],
    stages.query('/Product/Attribute[3]').value('/', 'varchar(max)') AS [Stage3],
    stages.query('/Product/Attribute[4]').value('/', 'varchar(max)') AS [Stage4]
FROM cte

Upvotes: 1

Related Questions