SQL列的拆分與合并
瀏覽量：2555
沈陽CRM定制，沈陽辦公OA，軟件開發，易勢科技最專業
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([name] varchar(1),[col] varchar(20),[cont] varchar(11))
insert [tb]
select 'a','1','aaa|bb|cc|d' union all
select 'b','1','aaa|bb|cc|d' union all
select 'c','1','aaa|bb|cc|d' union all
select 'd','2','aaa|bb|cc|d' union all
select 'e','2','aaa|bb|cc|d'
拆分：
with cte as(
select name, left(cont,charindex('|',cont+'|')-1) cont,substring(cont,charindex('|',cont)+1,len(cont)) cont1 from tb union all
select tb.name,
left(cte.cont1,charindex('|',cte.cont1+'|')-1) cont,substring(cte.cont1,charindex('|',cte.cont1+'|')+1,len(cte.cont1)) cont1
from tb ,cte where cte.name=tb.name and left(cte.cont1,charindex('|',cte.cont1+'|')-1)<>''
)select * from cte order by name,len(cont) desc
select name, left(cont,charindex('|',cont+'|')-1) cont,substring(cont,charindex('|',cont)+1,len(cont)) cont1 from tb union all
select tb.name,
left(cte.cont1,charindex('|',cte.cont1+'|')-1) cont,substring(cte.cont1,charindex('|',cte.cont1+'|')+1,len(cte.cont1)) cont1
from tb ,cte where cte.name=tb.name and left(cte.cont1,charindex('|',cte.cont1+'|')-1)<>''
)select * from cte order by name,len(cont) desc
合并：
with cte as(
select name,col,row_number() over(partition by col order by col ) row from tb
), cte1 as(
select cast(name as nvarchar(100)) name,col,row from cte where row=1 union all
select name,col,row_number() over(partition by col order by col ) row from tb
), cte1 as(
select cast(name as nvarchar(100)) name,col,row from cte where row=1 union all
select cast(cte1.name+','+cte.name as nvarchar(100)) name,cte.col,
cte.row from cte,cte1 where cte1.col=cte.col and cte.row=cte1.row+1
)select c.name,c.col from cte1 c where row=(select max(row) from cte1 where col=c.col )
cte.row from cte,cte1 where cte1.col=cte.col and cte.row=cte1.row+1
)select c.name,c.col from cte1 c where row=(select max(row) from cte1 where col=c.col )
一列分多列
select
name,
col,
字段5 = PARSENAME(REPLACE(cont,'|','.'),4), ----替換一下 '.' 因為 parsename 只認 '.'
字段6 = PARSENAME(REPLACE(cont,'|','.'),3),
字段7 = PARSENAME(REPLACE(cont,'|','.'),2),
字段8 = PARSENAME(REPLACE(cont,'|','.'),1)
from
tb
上一篇：SQL提取數字字母漢字
下一篇：html5-Canvas繪圖