SQL Server 中master..spt_values的应用

select number from master..spt_values with(nolock) where type=‘P‘ /**解释:master..spt_values表的字段值为P的对应number字段值是从0-2047*/ --1.将字符串转换为列显示 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