Split string into multiple columns TSQL

Question

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.

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

The real challenge is yielding a dynamic number of columns. Everyone will tell you this is a bad data model but perhaps you have no choice. Is there a finite number of comma seperated values that could be entered, i.e. 30? — Nick.Mc
– Nick.Mc, Commented Aug 10, 2021 at 12:31
This has been answered many times on stackoverflow, though admittedly it's difficult to trawl through all the incorrect answers (string_split is definitely not an answer). They all seem to point at this approach: jahaines.blogspot.com/2009/06/… See if you can apply the approach as explained in the link and post back if you have problems — Nick.Mc
– Nick.Mc, Commented Aug 10, 2021 at 12:41
Also, you're more likely to get an answer if you post a reproducible example (create table, statement, insert data into table). Also pictures are generally nor preferred for various reasons — Nick.Mc
– Nick.Mc, Commented Aug 10, 2021 at 12:42
Thanks, tried to apply from the blogspot post "Cannot find either column "Prod_Attributes" or the user-defined function or aggregate "Prod_Attributes.value", or the name is ambiguous." I guess because of the varying quantity of attributes. — Emma
– Emma, Commented Aug 10, 2021 at 13:12

Kristian Fitzgerald · Accepted Answer · 2021-08-11 02:50:03Z

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

persian-theme · Accepted Answer · 2021-08-11 01:48:11Z

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

Collectives™ on Stack Overflow

Split string into multiple columns TSQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related