我有sql表,因为我有2个字段为否和声明
Code Declaration 123 a1-2 nos,a2- 230 nos,a3 - 5nos
Code Declaration 123 a1 - 2nos 123 a2 - 230nos 123 a3 - 5nos
我需要将列数据拆分为该代码的行.
解决方法
对于这种类型的数据分离,我建议创建一个拆分函数：
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;
然后在查询中使用它,您可以使用外部应用程序加入到现有表中：
select t1.code,s.items declaration from yourtable t1 outer apply dbo.split(t1.declaration,',') s
这将产生结果：
| CODE | DECLARATION | ----------------------- | 123 | a1-2 nos | | 123 | a2- 230 nos | | 123 | a3 - 5nos |
或者您可以实现类似于此的CTE版本：
;with cte (code,DeclarationItem,Declaration) as
(
select Code,cast(left(Declaration,charindex(',Declaration+',')-1) as varchar(50)) DeclarationItem,stuff(Declaration,1,'),'') Declaration
from yourtable
union all
select code,'') Declaration
from cte
where Declaration > ''
)
select code,DeclarationItem
from cte
