CODEWITHSUNDEEP
sqlsql-server
Amresh Kumar Singh
Amresh Kumar Singh

Reputation: 133

SQL Server multiple rows and multiple column if one columns contains comma separated value

I have a data in SQL Server like below

productid      subproductid
---------     ----------------
1               1,2,3

I want output like this:

productid      subproductid
---------     ----------------
1               1
1               2
1               3

Thanks

Upvotes: 1

Views: 68

Answers (3)

Squirrel
Squirrel

Reputation: 24813

use STRING_SPLIT

SELECT *
FROM   yourtable t
       CROSS APPLY STRING_SPLIT (t.subproductid , ',')

Upvotes: 1

DxTx
DxTx

Reputation: 3377

Try this...

This answer is based on an article from sqlauthority.com website written by Pildev Dave.
https://blog.sqlauthority.com/2015/04/21/sql-server-split-comma-separated-list-without-using-a-function/

SELECT productid, 
       Ltrim(Rtrim(m.n.value('.[1]', 'nvarchar(max)'))) AS subproductid 
FROM   (SELECT productid, 
               Cast('<XMLRoot><RowData>' 
                    + Replace(subproductid, ',', '</RowData><RowData>') 
                    + '</RowData></XMLRoot>' AS XML) AS x 
        FROM   TableName) tbl 
       CROSS apply x.nodes('/XMLRoot/RowData')m(n)

Output

+-----------+--------------+
| productid | subproductid |
+-----------+--------------+
|         1 |            1 |
|         1 |            2 |
|         1 |            3 |
+-----------+--------------+

Online Demo: http://sqlfiddle.com/#!18/36420/2/0

Upvotes: 0

Hasnain Bukhari
Hasnain Bukhari

Reputation: 468

Till MSSQL SERVER 2014. STRING_SPLIT won't work,

What I have done, made a function that returned a splits values of columns and I have printed it.

USE [Database name]
GO
/****** Object:  UserDefinedFunction [dbo].[fnSplitString]    Script Date: 4/28/2018 2:39:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END

And then simply call that function

select productId,f1.* from [TableName]
Cross apply(select * from  fnSplitString(SubProductId,',') ) f1

This query works for me. and it will work for you. Thanks

Upvotes: 0

Related Questions