How to split value of column to dynamic column in SQL Server?

Question

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 Des : 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,&

and row 2 in column Des : 1-SKU: 512MBDDR2-533-1038,Sony VAIO PCG-1J1L 512MB DDR2-533 PC4200 Memory Module,&

and row 3 in column Des :1-MSI S271 1GB DDR2-533 PC4200 Memory Module,SKU: 1GBDDR2-533-1068,&2-MSI S271 512MB DDR2-533 PC4200 Memory Module,SKU: 512MBDDR2-533-1033,&

Output :

TBL_Sku2:

 id       sku1                     sku2                sku3                 sku4            sku5       
 ---     -----------       -----------------    -------------------  -----------------     ---------

 1   1GBDDR3-1066-21    2GBDDR3-1066-21        4GBDDR3-1066-414            Null               Null

 2   512MBDDR2-533-1038             null              null                     null            null
 3   1GBDDR2-533-1068        512MBDDR2-533-1033       Null                 Null                    Null

I want to professional function. Send for you my code later. Use in substring and charindex

sp :

ALTER PROCEDURE [dbo].[SP_SpilitSKU] 
(@ID int ,@SKU   nvarchar(Max) )

AS
BEGIN

Declare @YourTable table (ID int,SKU varchar(max))
Insert Into @YourTable values
(@ID,@SKU)

;with cte as (
      Select A.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 = LTrim(RTrim(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:%'      
)
INSERT INTO [dbo].[TBL_Sku]
           (id
           ,[SKU1]
           ,[SKU2]
           ,[SKU3]
           ,[SKU4]
           ,[SKU5]
           ,[SKU6]
           )
Select ID,[1] as SKU1,[2] as SKU2,[3] as SKU3,[4] as SKU4,[5] as SKU5,[6] as SKU6
From   cte
Pivot (max(SKU) For [RN] in ([1],[2],[3],[4],[5],[6]) ) p

SP :

ALTER PROCEDURE [dbo].[SP_CreateTableSku4Column] 


AS
BEGIN
declare @DesOrginal as nvarchar(max);
Declare @i as int = 0;
Declare @Count as int = (select COUNT(des) from TBL_Product_Test);
while (@i<= @Count)
Begin
WITH TBL_SKUTemp AS
(
    SELECT 
    ROW_NUMBER() OVER( ORDER BY (SELECT null)) AS 'RowNumber',[des]

    FROM TBL_Product_Test
) 
SELECT  @DesOrginal = [des]     
FROM TBL_SKUTemp 
WHERE RowNumber = @i+1;
set @i = @i+1;
EXECUTE  [dbo].[SP_SpilitSKU] @i,@DesOrginal ;
END

END

count column Des in the Table = 15563

affect in the TBL_Sku = 8449

(1 row(s) affected)
8449
15563
des = 1-Samsung NP940X5JI (NP940X5J-S01US) 4GB DDR3L-1600 PC12800 1.35V Memory Module,SKU: 4GBDDR3L-1600-9,&

(1 row(s) affected)

(0 row(s) affected)
8450
15563
des = 1-SKU: 512MBDDR2-533-1038,Sony VAIO PCG-1J1L 512MB DDR2-533 PC4200 Memory Module,&

other Method :

declare @DesOrginal as nvarchar(max);
Declare @i as int = 0;
Declare @Count as int = (select COUNT(des) from TBL_Product_Test);
while (@i<= @Count)
begin
WITH TBL_SKUTemp AS
(
    SELECT 
    ROW_NUMBER() OVER( ORDER BY (SELECT null)) AS 'RowNumber',[des]

    FROM TBL_Product_Test
) 
SELECT  @DesOrginal = [des]     
FROM TBL_SKUTemp 
WHERE RowNumber = @i+1;
set @i = @i+1;

DECLARE @str      VARCHAR(8000)=@DesOrginal,
        @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 * 
    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


  INSERT INTO [dbo].[TBL_Sku6]
           ([SKU1]
           ,[SKU2]
           ,[SKU3]
           ,[SKU4]
           ,[SKU5]
           )SELECT * 
    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 @i
print @Count
--print @str
--print RowNumber
print 'des = '+@DesOrginal
--PRINT @sql
----print SKU
----print RetVal
----print RetSeq
------print cte

end

TBL_Sku6:

TABLE [dbo].[TBL_Sku6](
    [SKU1] [varchar](7996) NULL,
    [SKU2] [varchar](7996) NULL,
    [SKU3] [varchar](7996) NULL,
    [SKU4] [varchar](7996) NULL,
    [SKU5] [varchar](7996) NULL,
    [SKU6] [varchar](7996) NULL
) ON [PRIMARY]

Error

The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

I think you need to first convert the string to table and then to pivot it . I have done same thing give me 5 min I will come up with solution — Yashveer Singh, Jan 15 '17 at 12:41
values are in the table @YashveerSingh , column type is nvarchar(max) — RedArmy, Jan 15 '17 at 12:44
Is it always 4 columns in result or based on number of `sku's` columns will be decided ? — Pரதீப், Jan 15 '17 at 12:45
with you current input sku 4 cannot be crated dynamically till sku3 is possible — Yashveer Singh, Jan 15 '17 at 12:53
@YashveerSingh , based on number of sku's columns will be decided — RedArmy, Jan 15 '17 at 12:58
You are right but possible value of one row is 4 sku or 3 sku or 2 sku but maximun sku in the on cell of column is 5 — RedArmy, Jan 15 '17 at 13:02
Read [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutly yes!** — Zohar Peled, Jan 15 '17 at 13:04
I'm voting to close this question as off-topic because no attempt to solve the problem has been demonstrated by the poster. Stack Overflow is not a code-writing service. — alroc, Jan 15 '17 at 14:00
You are right @alroc and i am sorry but i am beginner and attemp to solve and solve for one line but i want to learn best solution. — RedArmy, Jan 15 '17 at 14:28

score 1 · Answer 1 · answered Jan 15 '17 at 13:01

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

answered Jan 15 '17 at 13:01

Serge

3,986
2
17
37

Thanks @Serge , test and comeback – RedArmy Jan 15 '17 at 13:05
Good,thast ok. I was trying to by pivot :) – RedArmy Jan 15 '17 at 13:29
good work with any rows but how to get table and insert to TBL_SKU2 – RedArmy Jan 17 '17 at 10:33
`INSERT INTO tbl_SKU2 SELECT tvfn.SKU_1, tvfn.SKU_2, tvfn.SKU_3, tvfn.SKU_4 FROM TBL_SKU s CROSS APPLY dbo.tvfn_Extract_SKUs(s.SKU) tvfn` – Serge Jan 17 '17 at 10:46
i can :) . serge , when in on cell is 5 sku , sku is null – RedArmy Jan 17 '17 at 11:12
How many SKU's can you have on a single line? My answer works up to 4 SKU's. – Serge Jan 17 '17 at 11:13
Attemp and can but slow – RedArmy Jan 17 '17 at 13:02

Pரதீப் · Answer 2 · 2017-01-16T12:46:13.780

1

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/

edited Jan 16 '17 at 12:46

answered Jan 15 '17 at 13:15

Pரதீப்

91,748
19
131
172

Thanks @Prdp , Invalid object name 'dbo.Delimitedsplit8k'. – RedArmy Jan 15 '17 at 13:23
@RedArmy - I have attached the `dbo.Delimitedsplit8k` function code as well please create it and execute the query – Pரதீப் Jan 15 '17 at 13:24
see function and work good but not create Table and Save spile in the Table. – RedArmy Jan 15 '17 at 13:45
Very inefficient. Where are database fundamentals - for example working with sets? – Deadsheep39 Jan 15 '17 at 20:43
@Deadsheep39 - Lol, Did you see any loop/cursor in my answer. Plus according to OP the answer should be dynamic – Pரதீப் Jan 16 '17 at 01:48
@Prdp , i want to use pivot but i cannt to save in the table:( good work by your code. – RedArmy Jan 16 '17 at 08:44
@RedArmy - Do you want to insert the result of pivot into another table ? – Pரதீப் Jan 16 '17 at 09:01
@RedArmy - Why are inserting this result into a table. Because based on input number of columns will vary now we have 3 column. then for next input if 5 columns are generated from pivot then in which table you will insert ? – Pரதீப் Jan 16 '17 at 09:20
TBL_SKU A table with 6 columns – RedArmy Jan 16 '17 at 09:32
Edit Question my friend. – RedArmy Jan 16 '17 at 09:33
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/133266/discussion-between-redarmy-and-prdp). – RedArmy Jan 16 '17 at 12:31
No solution? Tbl_Product To TBL_SKU – RedArmy Jan 16 '17 at 12:33
@RedArmy - Sorry now question is not clear at all. Can you ask a new question with all the new requirements. – Pரதீப் Jan 16 '17 at 12:35
http://dba.stackexchange.com/questions/161199/how-to-split-value-of-column-and-insert-to-other-table-in-the-one-column-in-sql – RedArmy Jan 16 '17 at 12:39
@Prdp , If the table is made by pivot point can I save in another table or pivot point table is stored directly into the desired table to be solved – RedArmy Jan 16 '17 at 12:42
@RedArmy updated my answer.. `SELECT * into TBL_Sku2..` But when you execute the query more than once you will get error saying table name already exists. So make sure along table name `TBL_Sku2` add some session_id to make it unique – Pரதீப் Jan 16 '17 at 12:47
Insert Data in The TBL_SKU2? – RedArmy Jan 16 '17 at 12:50
insert one row but error `There is already an object named 'TBL_Sku2' in the database.` :))) – RedArmy Jan 16 '17 at 13:05
@RedArmy - Thats why i said make sure you give different name to tablename when you execute this query – Pரதீப் Jan 16 '17 at 13:06
i dont know, confused:(( insert my code to question and edit my code please – RedArmy Jan 16 '17 at 13:14
@RedArmy - You need to simplify your question and ask a clear and concise question showing your problem – Pரதீப் Jan 16 '17 at 13:16
@Prdp , Right but edit my questions, insert my code and show problem. – RedArmy Jan 16 '17 at 13:18

John Cappelletti · Answer 3 · 2017-01-15T23:46:03.610

1

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

edited Jan 15 '17 at 23:46

answered Jan 15 '17 at 13:31

John Cappelletti

79,615
7
44
66

Thanks @JohnCappelletti . – RedArmy Jan 15 '17 at 13:48
Working, but a little complicated. – Deadsheep39 Jan 15 '17 at 20:44
@Deadsheep39 The Cross Apply parses into rows and then we pivot – John Cappelletti Jan 15 '17 at 20:56
Yes, but I prefer working with xml when i convert string to xml. From use 2xcross apply, 1xpivot, 1xcast, 1xfor xml, 2xrownumber, 1xcte, 2xreplace you can do simple select with 1xreplace, Yxquery (for every output column). More in my answer (but output the same). – Deadsheep39 Jan 15 '17 at 21:04
@Deadsheep39 See the parse-row udf in (similar to yours) http://stackoverflow.com/questions/41075137/sql-server-split-string-to-row/41075500#41075500. The Parse-Row would have been my first choice, but there is too much noise in the string. You still have to find the SKU – John Cappelletti Jan 15 '17 at 21:19
@Deadsheep39 Get your answer working with OP's data and I'll be happy to up-vote it. – John Cappelletti Jan 15 '17 at 21:54
@JohnCappelletti , thanks for solution , run but affect 8480 row from 15566 row. use distinc? – RedArmy Jan 16 '17 at 08:37
Untill row number 8449 `des = 1-Samsung NP940X5JI (NP940X5J-S01US) 4GB DDR3L-1600 PC12800 1.35V Memory Module,SKU: 4GBDDR3L-1600-9,&` but since this row number `des = 1-SKU: 512MBDDR2-533-1038,Sony VAIO PCG-1J1L 512MB DDR2-533 PC4200 Memory Module,&` – RedArmy Jan 16 '17 at 09:21

Deadsheep39 · Answer 4 · 2017-01-15T20:51:02.303

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.

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&')

whole working with string

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

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.

How to split value of column to dynamic column in SQL Server?

4 Answers4

Linked