How To Split Value Of Column To Dynamic Column In Sql Server?

I want to create function to split the value of a column (sku) in TBL_Sku to any column (Sku1, 2, 3, 4, ...) in another table (TBL_Sku2) Column sku in the TBL_SkU: Row1 in column

Solution 1:

Here's a table-valued function you can use:

CREATEFUNCTIONdbo.tvfn_Extract_SKUs(
    @SKU_Line NVARCHAR(MAX)
)
RETURNSTABLEASRETURNWITHsku_startsAS (
        SELECT   CHARINDEX(':', @SKU_Line) + 2 AS SKU_1_Start
                ,CHARINDEX(':', @SKU_Line, CHARINDEX(':', @SKU_Line) + 2) + 2 AS SKU_2_Start
                ,CHARINDEX(':', @SKU_Line, CHARINDEX(':', @SKU_Line, CHARINDEX(':', @SKU_Line) + 2) + 2) + 2 AS SKU_3_Start
                ,CHARINDEX(':', @SKU_Line, CHARINDEX(':', @SKU_Line, CHARINDEX(':', CHARINDEX(':', @SKU_Line) + 2) + 2) + 2) + 2 AS SKU_4_Start
)
SELECTSUBSTRING(@SKU_Line, s.SKU_1_Start, CHARINDEX(',', @SKU_Line, s.SKU_1_Start) - s.SKU_1_Start) ASSKU_1
        ,CASEWHEN SKU_2_Start > SKU_1_Start THEN SUBSTRING(@SKU_Line, s.SKU_2_Start, CHARINDEX(',', @SKU_Line, s.SKU_2_Start) - s.SKU_2_Start) END SKU_2
        ,CASE WHEN SKU_3_Start > SKU_2_Start THEN SUBSTRING(@SKU_Line, s.SKU_3_Start, CHARINDEX(',', @SKU_Line, s.SKU_3_Start) - s.SKU_3_Start) END AS SKU_3
        ,CASE WHEN SKU_4_Start > SKU_3_Start THEN SUBSTRING(@SKU_Line, s.SKU_4_Start, CHARINDEX(',', @SKU_Line, s.SKU_4_Start) - s.SKU_4_Start) END AS SKU_4
FROM sku_starts s

Solution 2:

Variant 1. Example with delimiter &. When I see other answers I have to add some simple method. Script without data definition have only few rows. You should use simple and efective methods.

1. data definition

   if object_id('tempdb..#TblTestStr') isnotnulldroptable #TblTestStr
   createtable #TblTestStr (MyStr varchar(max))
   
   insertinto #TblTestStr values 
       ('Value1&Value2&Value3&Value4&'),
       ('Value2&Value3&Value0&'),
       ('Value1&'),
       ('Value3&Value4&')
   

2. whole working with string

   update #TblTestStr set MyStr ='<element>'+ replace(MyStr, '&','</element><element>') +'</element>'

3. data selection

   select 
       x.xcol.value('(./element)[1]', 'varchar(800)') col1,
       x.xcol.value('(./element)[2]', 'varchar(800)') col2,
       x.xcol.value('(./element)[3]', 'varchar(800)') col3,
       x.xcol.value('(./element)[4]', 'varchar(800)') col4
   from (selectcast(MyStr as xml) from #TblTestStr) x (xcol)
   

If you would like to use charindex and substring functions you have to check too much attributes.

Variant 2. One select statement.

select 
        x.xcol.value('(./element)[1]', 'varchar(800)'),
        x.xcol.value('(./element)[2]', 'varchar(800)'),
        x.xcol.value('(./element)[3]', 'varchar(800)'),
        x.xcol.value('(./element)[4]', 'varchar(800)')
    from (
        selectcast('<element>'+ replace(MyStr, '&','</element><element>') +'</element>'as xml) 
        from #TblTestStr) x (xcol)

Solution with xquery can be very efective and easily scalable.

Solution 3:

Here is one method which will PARSE and then PIVOT (not dynamic, but easy to expand to a max number of SKUs)

Declare@YourTabletable (ID int,SKU varchar(max))
InsertInto@YourTablevalues
(1,'1-Acer Aspire 3811TZG 1GB DDR3-1066 PC8500 Memory Module,SKU: 1GBDDR3-1066-21,&2-Acer Aspire 3811TZG 2GB DDR3-1066 PC8500 Memory Module,SKU: 2GBDDR3-1066-21,&3-Acer Aspire 3811TZG 4GB DDR3-1066 PC8500 Memory Module,SKU: 4GBDDR3-1066-414,&')

Select [ID],[Hits],[1] as SKU1,[2] as SKU2,[3] as SKU3,[4] as SKU4
From   (
        Select A.ID
              ,Hits =sum(1) over (PartitionBy ID)
              ,RN   =Row_Number() over (PartitionBy ID Orderby RetSeq) 
              ,SKU  = LTrim(RTrim(Replace(RetVal,'SKU:','')))
         From@YourTable A
         Cross Apply (
                      Select RetSeq =Row_Number() over (OrderBy (Selectnull))
                            ,RetVal = B.i.value('(./text())[1]', 'varchar(max)')
                      From  (Select x =Cast('<x>'+ replace((Select A.SKU as [*] For XML Path('')),',','</x><x>')+'</x>'as xml).query('.')) as A 
                      Cross Apply x.nodes('x') AS B(i)
                     ) B
          Where RetVal like'SKU:%'      
       ) S
Pivot (max(SKU) For [RN] in ([1],[2],[3],[4]) ) p

Returns

ID  Hits SKU1SKU2SKU3SKU4131GBDDR3-1066-212GBDDR3-1066-214GBDDR3-1066-414NULL

Solution 4:

Here is a dynamic approach

DECLARE@strVARCHAR(8000)='1-Acer Aspire 3811TZG 1GB DDR3-1066 PC8500 Memory Module,SKU: 1GBDDR3-1066-21,&2-Acer Aspire 3811TZG 2GB DDR3-1066 PC8500 Memory Module,SKU: 2GBDDR3-1066-21,&3-Acer Aspire 3811TZG 4GB DDR3-1066 PC8500 Memory Module,SKU: 4GBDDR3-1066-414,&',
        @col_list VARCHAR(1000)='',
        @sql      NVARCHAR(max)

SET@col_list =(SELECT Concat(',', Quotename(Concat('sku', Row_number()
                                                             OVER(
                                                               ORDERBY ItemNumber))))
                FROM   dbo.Delimitedsplit8k(@str, ',sku:')
                WHERE  Item LIKE'sku:%'FOR xml path(''))
SET@col_list = Stuff(@col_list, 1, 1, '')

SET@sql='SELECT * into TBL_Sku2
    FROM   (SELECT Concat(''sku'',Row_number()OVER(ORDER BY ItemNumber)) rn,
                   Stuff(item, 1, 5, '''')    AS item
            FROM   dbo.Delimitedsplit8k(@str, '',sku:'')
            WHERE  Item LIKE ''sku:%'') a
           PIVOT (Max(item)
                 FOR rn IN ('+@col_list +')) pv '

PRINT @sqlEXEC Sp_executesql
  @sql,
  N'@str VARCHAR(8000)',
  @str=@str

Result :

+-----------------+-----------------+------------------+
|      sku1       |      sku2       |       sku3       |
+-----------------+-----------------+------------------+
| 1GBDDR3-1066-21 | 2GBDDR3-1066-21 | 4GBDDR3-1066-414 |
+-----------------+-----------------+------------------+

Consider normalizing your table structure to parse data easier. Have a separate table for sku number and value

I have used split string function to split the records for each sku.

CreateFUNCTION [dbo].[DelimitedSplit8K]

        (@pStringVARCHAR(8000), @pDelimiterCHAR(1))
RETURNSTABLEWITH SCHEMABINDING ASRETURN--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...-- enough to cover NVARCHAR(4000)WITH E1(N) AS (
                 SELECT1UNIONALLSELECT1UNIONALLSELECT1UNIONALLSELECT1UNIONALLSELECT1UNIONALLSELECT1UNIONALLSELECT1UNIONALLSELECT1UNIONALLSELECT1UNIONALLSELECT1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT1FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT1FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front-- for both a performance gain and prevention of accidental "overruns"SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDERBY (SELECTNULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)SELECT1UNIONALLSELECT t.N+1FROM cteTally t WHERESUBSTRING(@pString,t.N,1) =@pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.SELECT ItemNumber =ROW_NUMBER() OVER(ORDERBY l.N1),
        Item       =SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;

Referred from http://www.sqlservercentral.com/articles/Tally+Table/72993/