This question already has an answer here:
这个问题在这里已有答案:
- How to split a comma-separated value to columns 31 answers
如何将逗号分隔值拆分为列31答案
I have the following data in a table. The number of values in each row can vary and the number of rows could also vary.
我在表格中有以下数据。每行中的值的数量可以变化,行的数量也可以变化。
The table has 1 column with csv formatted values. The values will always be numeric
该表有1列,带有csv格式的值。值始终为数字
Data
1,2
4
5,12, 10
6,7,8,9,10
15,17
I would like to end up with a temp table with the following
我想最终得到一个带有以下内容的临时表
Data Lowest Highest
1,2 1 2
4 4 4
5,12, 10 5 12
6,7,8,9,10 6 10
15,17 15 17
Can anyone help with writing a sql query or function to achieve this
任何人都可以帮助编写SQL查询或函数来实现这一目标
4 个解决方案
#1
2
Instead of function, you can achieve by this
而不是功能,你可以通过这个来实现
;WITH tmp
AS (SELECT A.rn,split.a.value('.', 'VARCHAR(100)') AS String
FROM (SELECT Row_number() OVER(ORDER BY (SELECT NULL)) AS RN,
Cast ('<M>' + Replace([data], ',', '</M><M>') + '</M>' AS XML) AS String
FROM table1) AS A
CROSS apply string.nodes ('/M') AS Split(a))
SELECT X.data,Tmp.lower,Tmp.higher
FROM (SELECT rn,Min(Cast(string AS INT)) AS Lower,Max(Cast(string AS INT)) AS Higher
FROM tmp
GROUP BY rn) Tmp
JOIN (SELECT Row_number() OVER(ORDER BY (SELECT NULL)) AS RN1,data
FROM table1) X
ON X.rn1 = Tmp.rn
Output would be:
输出将是:
Data Lower Higher
1,2 1 2
4 4 4
5,12, 10 5 12
6,7,8,9,10 6 10
15,17 15 17
#2
1
First create a user defined function to convert each row of 'DATA' column to a intermediate table as:
首先创建一个用户定义的函数,将'DATA'列的每一行转换为中间表,如下所示:
/****** Object: UserDefinedFunction [dbo].[CSVToTable]******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[CSVToTable] (@InStr VARCHAR(MAX))
RETURNS @TempTab TABLE
(id int not null)
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(id) VALUES (@VALUE)
END
RETURN
END
GO
Function is explained further here.
这里进一步解释了功能。
Then Using Cross Apply
we can get the desired output as:
然后使用Cross Apply我们可以获得所需的输出:
With CTE as
(
select
T.Data, Min(udf.Id) as [Lowest],Max(udf.Id) as [Highest]
from
Test T
CROSS APPLY dbo.CSVToTable(T.Data) udf
Group By Data
)
Select * from CTE
示例代码在这里......
What a Cross Apply
does is : it applies the right table expression to each row from the left table and produces a result table with the unified result sets.
Cross Apply的作用是:它将右表表达式应用于左表中的每一行,并生成具有统一结果集的结果表。
#3
0
Create table #temp1 (name varchar(100),value int )
Declare @len int
Select @len=(select max(LEN(name)-LEN(replace(name,',',''))) from table)
Declare @i int = 1
while (@i<=@len+1)
begin
insert into #temp1
select name,PARSENAME(REPLACE(name,',','.'),@i) from table t
set @i = @i+1
end
Select name,MIN(value) MINV,MAX(value) MAXV from #temp1 group by name
#4
0
declare @Testdata table ( Data varchar(max))
insert @Testdata select '1,2'
insert @Testdata select '4'
insert @Testdata select '5,12, 10'
insert @Testdata select '6,7,8,9,10'
;with tmp( DataItem, Data, RN1) as (
select LEFT(Data, CHARINDEX(',',Data+',')-1),
STUFF(Data, 1, CHARINDEX(',',Data+','), ''),
ROW_NUMBER()OVER(ORDER BY (SELECT NULL))AS RN1
from @Testdata
union all
select LEFT(Data, CHARINDEX(',',Data+',')-1),
STUFF(Data, 1, CHARINDEX(',',Data+','), ''),RN1
from tmp
where Data > ''
)
Select x.data,t.Low,t.Up FROM
(Select RN1,MIN(Cast(DataItem AS INT)) As Low,
MAX(Cast(DataItem AS INT)) As Up
FROM tmp t GROUP BY t.RN1)t
JOIN (Select ROW_NUMBER()OVER(ORDER BY (SELECT NULL))AS RN,data from @Testdata)X
ON X.RN = t.RN1
#1
2
Instead of function, you can achieve by this
而不是功能,你可以通过这个来实现
;WITH tmp
AS (SELECT A.rn,split.a.value('.', 'VARCHAR(100)') AS String
FROM (SELECT Row_number() OVER(ORDER BY (SELECT NULL)) AS RN,
Cast ('<M>' + Replace([data], ',', '</M><M>') + '</M>' AS XML) AS String
FROM table1) AS A
CROSS apply string.nodes ('/M') AS Split(a))
SELECT X.data,Tmp.lower,Tmp.higher
FROM (SELECT rn,Min(Cast(string AS INT)) AS Lower,Max(Cast(string AS INT)) AS Higher
FROM tmp
GROUP BY rn) Tmp
JOIN (SELECT Row_number() OVER(ORDER BY (SELECT NULL)) AS RN1,data
FROM table1) X
ON X.rn1 = Tmp.rn
Output would be:
输出将是:
Data Lower Higher
1,2 1 2
4 4 4
5,12, 10 5 12
6,7,8,9,10 6 10
15,17 15 17
#2
1
First create a user defined function to convert each row of 'DATA' column to a intermediate table as:
首先创建一个用户定义的函数,将'DATA'列的每一行转换为中间表,如下所示:
/****** Object: UserDefinedFunction [dbo].[CSVToTable]******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[CSVToTable] (@InStr VARCHAR(MAX))
RETURNS @TempTab TABLE
(id int not null)
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(id) VALUES (@VALUE)
END
RETURN
END
GO
Function is explained further here.
这里进一步解释了功能。
Then Using Cross Apply
we can get the desired output as:
然后使用Cross Apply我们可以获得所需的输出:
With CTE as
(
select
T.Data, Min(udf.Id) as [Lowest],Max(udf.Id) as [Highest]
from
Test T
CROSS APPLY dbo.CSVToTable(T.Data) udf
Group By Data
)
Select * from CTE
示例代码在这里......
What a Cross Apply
does is : it applies the right table expression to each row from the left table and produces a result table with the unified result sets.
Cross Apply的作用是:它将右表表达式应用于左表中的每一行,并生成具有统一结果集的结果表。
#3
0
Create table #temp1 (name varchar(100),value int )
Declare @len int
Select @len=(select max(LEN(name)-LEN(replace(name,',',''))) from table)
Declare @i int = 1
while (@i<=@len+1)
begin
insert into #temp1
select name,PARSENAME(REPLACE(name,',','.'),@i) from table t
set @i = @i+1
end
Select name,MIN(value) MINV,MAX(value) MAXV from #temp1 group by name
#4
0
declare @Testdata table ( Data varchar(max))
insert @Testdata select '1,2'
insert @Testdata select '4'
insert @Testdata select '5,12, 10'
insert @Testdata select '6,7,8,9,10'
;with tmp( DataItem, Data, RN1) as (
select LEFT(Data, CHARINDEX(',',Data+',')-1),
STUFF(Data, 1, CHARINDEX(',',Data+','), ''),
ROW_NUMBER()OVER(ORDER BY (SELECT NULL))AS RN1
from @Testdata
union all
select LEFT(Data, CHARINDEX(',',Data+',')-1),
STUFF(Data, 1, CHARINDEX(',',Data+','), ''),RN1
from tmp
where Data > ''
)
Select x.data,t.Low,t.Up FROM
(Select RN1,MIN(Cast(DataItem AS INT)) As Low,
MAX(Cast(DataItem AS INT)) As Up
FROM tmp t GROUP BY t.RN1)t
JOIN (Select ROW_NUMBER()OVER(ORDER BY (SELECT NULL))AS RN,data from @Testdata)X
ON X.RN = t.RN1