sql表记录集分列

Question

一个表中有多条这样的记录
fnumber,fname,femail
ah.0017,安徽新希望,ah0091/ah0130/ah0132
要转换成这这种格式：
ah.0017,安徽新希望,ah0091
ah.0017,安徽新希望,ah0130
ah.0017,安徽新希望,ah0132

Answer 1

2005SELECT
    A.fnumber,A.name,
    B.x.value('.','varchar(50)') AS femail
FROM (
    SELECT *,flag = CONVERT(xml,'<v>'+REPLACE(femail,'/','</v><v>')+'</v>')
    FROM tb
) AS A
    OUTER APPLY A.flag.nodes('//v') AS B(x);

Answer 2

----------------------------------------------------------------
-- Author :fredrickhu(小F 向高手学习)
-- Date   :2009-08-22 15:36:28
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([fnumber] varchar(7),[fname] varchar(10),[femail] varchar(20))
insert [tb]
select 'ah.0017','安徽新希望','ah0091/ah0130/ah0132'
--------------开始查询--------------------------
if object_id('Tempdb..#Num') is not null
    drop table #Num
go
select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b
Select 
    a.fnumber,a.fname,femail=substring(a.femail,b.ID,charindex('/',a.femail+'/',b.ID)-b.ID) 
from 
    Tb a,#Num b
where
    charindex('/','/'+a.femail,b.ID)=b.ID 
----------------结果----------------------------
/*fnumber fname      femail
------- ---------- --------------------
ah.0017 安徽新希望      ah0091
ah.0017 安徽新希望      ah0130
ah.0017 安徽新希望      ah0132(3 行受影响)*/

Answer 3

字符串分拆？--各种字符串分函数if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO--3.2.1 循环截取法
CREATE FUNCTION f_splitSTR(
@s   varchar(8000),   --待分拆的字符串
@split varchar(10)     --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
    DECLARE @splitlen int
    SET @splitlen=LEN(@split+'a')-2
    WHILE CHARINDEX(@split,@s)>0
    BEGIN
        INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
        SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
    END
    INSERT @re VALUES(@s)
    RETURN
END
GO
/*==============================================*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO--3.2.3.1 使用临时性分拆辅助表法
CREATE FUNCTION f_splitSTR(
@s   varchar(8000),  --待分拆的字符串
@split varchar(10)     --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
    --创建分拆处理的辅助表(用户定义函数中只能操作表变量)
    DECLARE @t TABLE(ID int IDENTITY,b bit)
    INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b    INSERT @re SELECT SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID)
    FROM @t
    WHERE ID<=LEN(@s+'a') 
        AND CHARINDEX(@split,@split+@s,ID)=ID
    RETURN
END
GO/*==============================================*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tb_splitSTR]') and objectproperty(id,N'IsUserTable')=1)
drop table [dbo].[tb_splitSTR]
GO--3.2.3.2 使用永久性分拆辅助表法
--字符串分拆辅助表
SELECT TOP 8000 ID=IDENTITY(int,1,1) INTO dbo.tb_splitSTR
FROM syscolumns a,syscolumns b
GO--字符串分拆处理函数
CREATE FUNCTION f_splitSTR(
@s     varchar(8000),  --待分拆的字符串
@split  varchar(10)     --数据分隔符
)RETURNS TABLE
AS
RETURN(
    SELECT col=CAST(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID) as varchar(100))
    FROM tb_splitSTR
    WHERE ID<=LEN(@s+'a') 
        AND CHARINDEX(@split,@split+@s,ID)=ID)
GO
/*==============================================*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO--3.2.5 将数据项按数字与非数字再次拆份
CREATE FUNCTION f_splitSTR(
@s   varchar(8000),    --待分拆的字符串
@split varchar(10)     --数据分隔符
)RETURNS @re TABLE(No varchar(100),Value varchar(20))
AS
BEGIN
    --创建分拆处理的辅助表(用户定义函数中只能操作表变量)
    DECLARE @t TABLE(ID int IDENTITY,b bit)
    INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b    INSERT @re 
    SELECT    No=REVERSE(STUFF(col,1,PATINDEX('%[^-^.^0-9]%',col+'a')-1,'')),
        Value=REVERSE(LEFT(col,PATINDEX('%[^-^.^0-9]%',col+'a')-1))
    FROM(
        SELECT col=REVERSE(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID))
        FROM @t
        WHERE ID<=LEN(@s+'a') 
            AND CHARINDEX(@split,@split+@s,ID)=ID)a
    RETURN
END
GO
/*==============================================*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO--3.2.6 分拆短信数据
CREATE FUNCTION f_splitSTR(@s varchar(8000))
RETURNS @re TABLE(split varchar(10),value varchar(100))
AS
BEGIN
    DECLARE @splits TABLE(split varchar(10),splitlen as LEN(split))
    INSERT @splits(split)
    SELECT 'AC' UNION ALL
    SELECT 'BC' UNION ALL
    SELECT 'CC' UNION ALL
    SELECT 'DC'    
    DECLARE @pos1 int,@pos2 int,@split varchar(10),@splitlen int
    SELECT TOP 1 
        @pos1=1,@split=split,@splitlen=splitlen
    FROM @splits
    WHERE @s LIKE split+'%'
    WHILE @pos1>0
    BEGIN
        SELECT TOP 1
            @pos2=CHARINDEX(split,@s,@splitlen+1)
        FROM @splits
        WHERE CHARINDEX(split,@s,@splitlen+1)>0
        ORDER BY CHARINDEX(split,@s,@splitlen+1)
        IF @@ROWCOUNT=0
        BEGIN
            INSERT @re VALUES(@split,STUFF(@s,1,@splitlen,''))
            RETURN
        END
        ELSE
        BEGIN
            INSERT @re VALUES(@split,SUBSTRING(@s,@splitlen+1,@pos2-@splitlen-1))
            SELECT TOP 1 
                @pos1=1,@split=split,@splitlen=splitlen,@s=STUFF(@s,1,@pos2-1,'')
            FROM @splits
            WHERE STUFF(@s,1,@pos2-1,'') LIKE split+'%'
        END
    END
    RETURN
END
GO

Answer 4

--2005
----------------------------------------------------------------
-- Author :fredrickhu(小F 向高手学习)
-- Date   :2009-08-22 15:36:28
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([fnumber] varchar(7),[fname] varchar(10),[femail] varchar(20))
insert [tb]
select 'ah.0017','安徽新希望','ah0091/ah0130/ah0132'
--------------开始查询--------------------------
SELECT
    a.fnumber,a.fname,
    b.x.value('.','varchar(50)') AS femail
FROM (
    SELECT *,flag = CONVERT(xml,'<v>'+REPLACE(femail,'/','</v><v>')+'</v>')
    FROM tb
) AS A
    OUTER APPLY A.flag.nodes('//v') AS B(x)
----------------结果----------------------------
/*fnumber fname      femail
------- ---------- --------------------
ah.0017 安徽新希望      ah0091
ah.0017 安徽新希望      ah0130
ah.0017 安徽新希望      ah0132(3 行受影响)*/

Answer 5

if object_id('[tb]') is not null 
drop table [tb]
go
create table [tb]([fnumber] varchar(7),[fname] varchar(10),[femail] varchar(20))
insert [tb]
select 'ah.0017','安徽新希望','ah0091/ah0130/ah0132'
go
Select
    a.[fnumber],
    a.[fname],
    [femail]=substring(a.[femail],b.number,charindex('/',a.[femail]+'/',b.number)-b.number) 
from 
    [tb] a join master..spt_values  b 
    ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.[femail])
where
     substring('/'+a.[femail],b.number,1)='/'
     /*
     fnumber fname      femail
------- ---------- --------------------
ah.0017 安徽新希望      ah0091
ah.0017 安徽新希望      ah0130
ah.0017 安徽新希望      ah0132     */
     

Answer 6

--2005的方法:
----------------------------------------------------------------
-- Author :fredrickhu(小F 向高手学习)
-- Date   :2009-08-22 15:36:28
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([fnumber] varchar(7),[fname] varchar(10),[femail] varchar(20))
insert [tb]
select 'ah.0017','安徽新希望','ah0091/ah0130/ah0132'
--------------开始查询--------------------------
;with fredrickhu as 
(select 
fnumber,fname,femail=cast(left(femail,charindex('/',femail+'/')-1) as nvarchar(100)),
Split=cast(stuff(femail+'/',1,charindex('/',femail+'/'),'') as nvarchar(100)) from Tb
union all
select fnumber,fname,femail=cast(left(Split,charindex('/',Split)-1) as nvarchar(100)),
Split= cast(stuff(Split,1,charindex('/',Split),'') as nvarchar(100)) from fredrickhu where split>''
)
select fnumber,fname,femail from fredrickhu order by fnumber option (MAXRECURSION 0)
----------------结果----------------------------
/*fnumber fname      femail
------- ---------- --------------------
ah.0017 安徽新希望      ah0091
ah.0017 安徽新希望      ah0130
ah.0017 安徽新希望      ah0132(3 行受影响)*/

Answer 7

if object_id('ta')is not null 
drop table ta
go
create  table ta(fnumber  varchar(20),fname  varchar(20),femail    varchar(20)    )
insert ta select
'ah.0017','安徽新希望','ah0091/ah0130/ah0132' 
declare @str varchar(20),@sql varchar(4000)
set @str=(select femail from ta)set @sql='select ''ah.0017'' as fnumber,''安徽新希望'' as fname,'''+replace(@str,'/',''' as femail  union all select ''ah.0017'',''安徽新希望'',''' )
set @sql=@sql+''''--print @sql
exec(@sql)fnumber fname      femail
------- ---------- ------
ah.0017 安徽新希望      ah0091
ah.0017 安徽新希望      ah0130
ah.0017 安徽新希望      ah0132(3 行受影响)77的这招我也学会了

Answer 8

借花献佛
只要符合此种规则, 而数据不是很大, 可重复使用
-- 先封装一个函数
if( object_id('fn_分列') is not null )
drop function fn_分列
go
create function fn_分列( @cF1 varchar(40), @cF2 varchar(40), @cVal varchar(200))
returns varchar(8000)
as
begin
declare @cL varchar(40)
declare @cR varchar(40)
declare @cSQL varchar(8000)
set @cL = '''' + @cF1 + ''',' + '''' + @cF2 + ''','''
set @cR = @cVal set @cSQL = 'select' + @cL + replace( @cR, '/', ''' union all select ' + @cL ) + ''''
return( @cSQL )
end
go-- 调用执行
declare @cSQL varchar(8000)
select @cSQL = dbo.fn_分列('ah.0017', '安徽新希望', 'ah0091/ah0130/ah0132')
exec( @cSQL )
-- 遗憾, MSSQL的动态执行结果只能insert into到物理表或临时表, 不能到表变量中
-- 否则一个函数返回全搞定, 不用返回后再调用exec才能满足-- 执行结果
                          
------- ---------- ------ 
ah.0017 安徽新希望      ah0091
ah.0017 安徽新希望      ah0130
ah.0017 安徽新希望      ah0132

Answer 9

带逗号的(,)declare @cSQL varchar(8000)
select @cSQL = dbo.fn_分列('ah.0017,', '安徽新希望,', 'ah0091/ah0130/ah0132')
exec( @cSQL )-- 结果集
                            
-------- ----------- ------ 
ah.0017, 安徽新希望,      ah0091
ah.0017, 安徽新希望,      ah0130
ah.0017, 安徽新希望,      ah0132