字段分隔符，一行转多行数据库操作

SqlServer数据库

基础表 master..spt_values

select * from master..spt_values；

-- 解释: master..spt_values表的字段type值为P时, 对应number字段值是从0-2047
select number from master..spt_values with(nolock) where type='P'；

基础函数

charindex

-- charindex([通配符], 目标字符串/字段名称, 开始下标)
select charindex('2', '123456789', 1);  -- 2
select charindex('2', '1223334444', 2); -- 2
select charindex('2', '1223334444', 3) ; -- 3
select charindex('2', '1223334444', 4) ; -- 0

substring

-- substring(目标字符串/字段名称, 截取开始下标, 截取长度)
select substring('abcdefg', 1, 1); -- a
select substring('abcdefg', 1, 2); -- ab
select substring('abcdefg', 1, 3); -- abc
select substring('abcdefg', 4, 10); -- defg

1. 将字符串转换为列显示

sql模板

• 此处分隔符是 “,”

select distinct a.[字段名1], a.[字段明2],
      [被分割的字段名]=substring(a.header, b.number, charindex(',', a.header+',', b.number)-b.number)
from [表名] a
join master..spt_values b 
on b.type='P'
where charindex(',', ','+a.[[被分割的字段名]], b.number) = b.number;

实例：

点击查看代码

if object_id('tb') is not null drop table tb 
go 
create table tb([编号] varchar(3),[产品] varchar(2),[数量] int,[单价] int,[金额] int,[序列号] varchar(8)) 
insert into tb([编号],[产品],[数量],[单价],[金额],[序列号]) 
select '001','AA',3,5,15,'12,13,14' union all
select '002','BB',8,9,13,'22,23,24'
go 
select [编号], [产品], [数量], [单价], [金额],
			 substring([序列号],b.number,charindex(',',[序列号]+',',b.number)-b.number) as [序列号] 
from tb a with(nolock), master..spt_values b with(nolock) 
where b.number >= 1 
and b.number < len(a.[序列号]) 
and b.type='P'
and substring(',' + [序列号] , number, 1) = ','
go 
drop table tb 
go 
/** 
编号   产品   数量        单价        金额      序列号 
---- ---- ----------- ----------- ----------- -------- 
001    AA      3           5          15          12 
001    AA      3           5          15          13 
001    AA      3           5          15          14 
002    BB      8           9          13          22 
002    BB      8           9          13          23 
002    BB      8           9          13          24 
*/ 
----------