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:

CREATE FUNCTION dbo.tvfn_Extract_SKUs(
    @SKU_Line NVARCHAR(MAX)
)
RETURNS TABLE
AS
RETURN

WITH sku_starts AS (
        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
)
SELECT   SUBSTRING(@SKU_Line, s.SKU_1_Start, CHARINDEX(',', @SKU_Line, s.SKU_1_Start) - s.SKU_1_Start) AS SKU_1
        ,CASE WHEN 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') is not null drop table #TblTestStr
   create table #TblTestStr (MyStr varchar(max))
   
   insert into #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 (select cast(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 (
        select cast('<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 @YourTable table (ID int,SKU varchar(max))
Insert Into @YourTable values
(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 (Partition By ID)
              ,RN   = Row_Number() over (Partition By ID Order by RetSeq) 
              ,SKU  = LTrim(RTrim(Replace(RetVal,'SKU:','')))
         From @YourTable A
         Cross Apply (
                      Select RetSeq = Row_Number() over (Order By (Select null))
                            ,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 SKU1               SKU2              SKU3                SKU4
1   3    1GBDDR3-1066-21    2GBDDR3-1066-21   4GBDDR3-1066-414    NULL

Solution 4:

Here is a dynamic approach

DECLARE @str      VARCHAR(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(
                                                               ORDER BY 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 @sql

EXEC 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.

Create FUNCTION [dbo].[DelimitedSplit8K]

        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
     -- enough to cover NVARCHAR(4000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM 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 (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@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(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;

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