拆分列值 [英] split the column values

问题描述

我有下表



sno数据

1德里，孟买，加尔各答

2班加罗尔，chennai



我的输出应该是



sno数据

1 delhi

1 bombay

1加尔各答

2班加罗尔

2 chennai



我该如何执行此操作？

i have the following table

sno data
1 del bombay, calcutta
2 bangalore, chennai

my output should be

sno data
1 delhi
1 bombay
1 calcutta
2 bangalore
2 chennai

how can i perform this?

推荐答案

如果你看一下这个：在SQL IN子句中使用逗号分隔值参数字符串 [ ^ ] - 当你使用时它用于IN子句参数r传递逗号分隔列表，但该函数返回一个表，因此您可以很容易地修改它以生成输出。然后你只需从表中选择返回值。



OK ...试试这个：

创建SQL函数：

If you have a look at this: Using comma separated value parameter strings in SQL IN clauses[^] - it's intended for IN clauses when you use a parameter to pass the comma delimited list, but the function returns a table, so it would be simple for you to modify that to produce your output. You then just SELECT from the table to return the values.

OK...try this:
Create the SQL function:

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[CSVToTableWithId] (@ID INT, @InStr VARCHAR(MAX))
RETURNS @TempTab TABLE
   (tempid int IDENTITY(1,1) not null, 
   sno int not null,
   data NVARCHAR(MAX))
AS
BEGIN
    ;-- Ensure input ends with comma
	SET @InStr = REPLACE(@InStr + ',', ',,', ',')
	DECLARE @SP INT
DECLARE @VALUE VARCHAR(1000)
WHILE PATINDEX('%,%', @INSTR ) <> 0 
BEGIN
   SELECT  @SP = PATINDEX('%,%',@INSTR)
   SELECT  @VALUE = LEFT(@INSTR , @SP - 1)
   SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')
   INSERT INTO @TempTab(sno, data) VALUES (@ID,@VALUE)
END
	RETURN
END
GO

然后，使用CROSS APPLY获取值：

Then, use CROSS APPLY to fetch the values:

SELECT ca.sno, ca.data FROM MyTable t
CROSS APPLY (SELECT * FROM [CSVToTableWithId](t.sno, t.data)) ca

查看我过去的回答：表格单元格包含用逗号分隔的多个值，例如A，B，C，D，A，E，C，然后如何删除重复值 [ ^ ]



和工作示例：

See my past answer: A table cell contains multiple values separated by commas e.g A,B,C,D,A,E,C then how to remove the duplicate values[^]

And working example:

DECLARE @tmp TABLE (sno INT, data VARCHAR(30))

INSERT INTO @tmp (sno, data)
VALUES(1, 'delhi, bombay, calcutta'),
(2, 'bangalore, chennai')


;WITH CitiesCTE AS
(
    SELECT sno, LEFT(data,CHARINDEX(',', data)-1) AS City, LTRIM(RTRIM(RIGHT(data,LEN(data) - CHARINDEX(',', data)))) AS Remainder
    FROM @tmp
    WHERE CHARINDEX(',', data)>0
    UNION ALL
    SELECT sno, LEFT(Remainder,CHARINDEX(',', Remainder)-1) AS City, LTRIM(RTRIM(RIGHT(Remainder,LEN(Remainder) - CHARINDEX(',', Remainder)))) AS Remainder
    FROM CitiesCTE
    WHERE CHARINDEX(',', Remainder)>0
    UNION ALL
    SELECT sno, Remainder AS City, NULL AS Remainder
    FROM CitiesCTE
    WHERE CHARINDEX(',', Remainder)=0
)
SELECT *
FROM CitiesCTE
ORDER BY sno

结果：

Result:

1	delhi		bombay, calcutta
1	bombay		calcutta
1	calcutta	NULL
2	bangalore	chennai
2	chennai		NULL

这篇关于拆分列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！