I have a SQL Server table like this:

I want to split the column items value to a new row for each one item like this:

CodePudding user response：

WITH CTE(id ,description,items) AS
(
 SELECT 123, 'Women clothing' , 'T-shirt & Bottom & Top' UNION ALL
 SELECT 124 ,'sports items' ,'badminton racket'UNION ALL
 SELECT 125 ,'gadgets' ,'iphone & airpod & charging'
)
SELECT C.ID,C.DESCRIPTION,VALUE
FROM CTE AS C
CROSS APPLY STRING_SPLIT(C.items, '&'); 

CodePudding user response：

If you dont have string_split func. (below sqlserver2016) you can use this function , its working in our production enviroment for years :

USE xxx
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[StringSplit]
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN

  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
                ),                          
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), 
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), 
 cteTally(N) AS (
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (
                 SELECT 1 UNION ALL
                 SELECT t.N 1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;

and query :

select * from 
(SELECT 123 id,      'T-shirt & Bottom & Top' T UNION ALL
 SELECT 124 ,     'badminton racket'UNION ALL
 SELECT 125 , 'iphone & airpod & charging' ) x
CROSS APPLY dbo.StringSplit(x.T, '&')