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';
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
*/
----------