将列数据拆分为行的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13527537/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL query to split column data into rows
提问by Affan
I am having sql table in that I am having 2 fields as Noand declaration
我有 sql 表，因为我有 2 个字段No和declaration
Code Declaration
123 a1-2 nos, a2- 230 nos, a3 - 5nos
I need to display the declaration for that code as:
我需要将该代码的声明显示为：
Code Declaration
123 a1 - 2nos
123 a2 - 230nos
123 a3 - 5nos
I need to split the column data to rows for that code.
我需要将列数据拆分为该代码的行。
回答by Taryn
For this type of data separation, I would suggest creating a split function:
对于这种类型的数据分离，我建议创建一个拆分函数：
create FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))
returns @temptable TABLE (items varchar(MAX))
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end;
Then to use this in a query you can use an outer applyto join to your existing table:
然后要在查询中使用它，您可以使用 anouter apply加入现有表：
select t1.code, s.items declaration
from yourtable t1
outer apply dbo.split(t1.declaration, ',') s
Which will produce the result:
这将产生结果：
| CODE | DECLARATION |
-----------------------
| 123 | a1-2 nos |
| 123 | a2- 230 nos |
| 123 | a3 - 5nos |
Or you can implement a CTE version similar to this:
或者您可以实现与此类似的 CTE 版本：
;with cte (code, DeclarationItem, Declaration) as
(
select Code,
cast(left(Declaration, charindex(',',Declaration+',')-1) as varchar(50)) DeclarationItem,
stuff(Declaration, 1, charindex(',',Declaration+','), '') Declaration
from yourtable
union all
select code,
cast(left(Declaration, charindex(',',Declaration+',')-1) as varchar(50)) DeclarationItem,
stuff(Declaration, 1, charindex(',',Declaration+','), '') Declaration
from cte
where Declaration > ''
)
select code, DeclarationItem
from cte
回答by Niladri Biswas
Declare @t Table([Code] int, [Declaration] varchar(32));
Insert Into @t([Code], [Declaration])
Values(123, 'a1-2 nos, a2- 230 nos, a3 - 5nos')
Select
x.[Code]
,t.Declaration
From
(
Select
*,
Cast('<X>'+Replace(t.[Declaration],',','</X><X>')+'</X>' As XML) As record
From @t t
)x
Cross Apply
(
Select fdata.D.value('.','varchar(50)') As Declaration
From x.record.nodes('X') As fdata(D)
) t
Few times back , I have blogged about the same Split Function in Sql Server using Set base approach
前几次，我在博客中使用 Set base 方法在 Sql Server 中写了相同的拆分函数
Also, please visit Erland Sommarskogblog who is maintaining the answer for the same since the last 15 years.
另外，请访问Erland Sommarskog博客，他自过去 15 年来一直保持相同的答案。
回答by Ambareesh Surendran
Try this....
尝试这个....
declare @col1 varchar(100),@CurentSubString varchar(100)
create table #temp
(
col1 varchar(50)
)
DECLARE CUR CURSOR
FOR SELECT col1
FROM your_table
open CUR
FETCH next
FROM CUR
INTO @col1
WHILE @@FETCH_STATUS = 0
BEGIN
WHILE CHARINDEX (@col1, ';') <> 0
BEGIN
SET @CurentSubString = SUBSTRING(@col1,1,CHARINDEX (@col1, ';'))
SET @col1 = SUBSTRING(@col1,CHARINDEX (@col1, ';')+1,len(@col1))
insert into #temp
select @CurentSubString
END
IF CHARINDEX (@col1, ';') = 0 and isnull(@col1,'')!= ''
BEGIN
INSERT INTO #temp
SELECT @col1
END
FETCH next
FROM CUR
INTO @col1
END
select *
From #temp
CLOSE CUR
DEALLOCATE CUR
