----------------------------
-- Author :DBA_Huangzj()
-- Date :2013-09-17 15:03:36
-- Version:
-- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
--Jun 10 2013 20:09:10
--Copyright (c) Microsoft Corporation
--Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([编号] varchar(3),[产品] varchar(2),[数量] int,[单价] int,[金额] int,[序列号] varchar(8))
insert [huang]
select '001','AA',3,5,15,'12,13,14' union all
select '002','bb',3,5,15,'22,23,24'
--------------开始查询--------------------------
select 编号 , 产品 , 数量, 单价 , 金额,SUBSTRING([序列号],number,CHARINDEX(',',[序列号]+',',number)-number) as [序列号]
from [huang] a,master..spt_values
where
number >=1 and number<len([序列号])
and type='p'
and substring(','+[序列号],number,1)=','
----------------结果----------------------------
/*
编号 产品 数量 单价 金额 序列号
---- ---- ----------- ----------- ----------- --------
001 AA 3 5 15 12
001 AA 3 5 15 13
001 AA 3 5 15 14
002 bb 3 5 15 22
002 bb 3 5 15 23
002 bb 3 5 15 24
*/