SQL Server多行和多列如果一列包含逗号分隔的值

Question

我在sql Server中有一个数据，如下

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

我想要这样的输出：

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

谢谢

Answer 1

使用string_split

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

Answer 2

直到MSSQL Server 2014. String_split无法正常工作，

我所做的工作，制作了一个返回列的拆分值的函数，我已经打印了它。

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

，然后简单地调用该功能

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

此查询对我有用。它将对您有用。谢谢

Answer 3

尝试这个...

此答案基于Pildev Dave撰写的SQLauthority.com网站的文章。
https://blog.sqlauthority.com/2015/04/21/sql-server-split-split-comma-separated-list-list-without-rist-usis-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) 

输出

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

在线演示：http：//sqlfiddle.com/#！18/36420/2/0