行转列问题

Small__Wolf 2009-09-03 05:48:11

格式如下

id name
1 N1,N2,N3

要求转换出来的结果为

ID NAME
1 N1
2 N2
3 N3

...全文

68 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

lihan6415151528 2009-09-03

打赏
举报



create function f_split(@SourceSql varchar(8000),@StrSeprate varchar(10))

returns @temp table(a varchar(100))

--实现split功能 的函数



as 

begin

    declare @i int

    set @SourceSql=rtrim(ltrim(@SourceSql))

    set @i=charindex(@StrSeprate,@SourceSql)

    while @i>=1

    begin

        insert @temp values(left(@SourceSql,@i-1))

        set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)

        set @i=charindex(@StrSeprate,@SourceSql)

    end

    if @SourceSql<>'' 

       insert @temp values(@SourceSql)

    return 

end





select * from dbo.f_split('N1,N2,N3 ',',')



a                                                                                                    

------------------------------------------ 

N1

N2

N3



（所影响的行数为 3 行）

Small__Wolf 2009-09-03

打赏
举报

以上的方法都没有问题，我想把这个写到一个函数里，如何写

soft_wsx 2009-09-03

打赏
举报

SQL SERVER 2000可正常通过

soft_wsx 2009-09-03

打赏
举报





/*

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)   Jul  9 2008 14:43:34   Copyright (c) 

1988-2008 Microsoft Corporation  Enterprise Evaluation Edition on Windows NT 5.1 <X86> 

(Build 2600: Service Pack 3) 

 愿和大家共同进步

如有雷同、实属巧合

●●●●●2009-09-03 18:20:36.077●●●●●

　★★★★★soft_wsx★★★★★

*/

if OBJECT_ID('tb') is not null drop table tb

go

create table tb(id int identity(100,1),col nvarchar(20))

go

insert tb(col)

select  'N1,N2,N3'

union all select 'N2,N3,N4'



--分拆处理(由于COL；列定义只有50字节，所以50个记录的分表就可以了)

select top 50 id=identity(int,1,1)

  into #ab

     from dbo.syscolumns a,dbo.syscolumns b



select identity(int,1,1) as 编号,分拆结果=substring(a.col,b.id,charindex(',',a.col+',',b.id)-b.id) into #k

  from tb a(nolock),#ab b(nolock)

  where charindex(',',','+a.col,b.id)=b.id

        and b.id<=len(a.col+'a')

drop table #ab

drop table tb





select * from #k



/*

编号	分拆结果

1	N1

2	N2

3	N3

4	N2

5	N3

6	N4

*/

好了！通过临时表分拆数据到表

--小F-- 2009-09-03

打赏
举报

----------------------------------------------------------------

-- Author  :fredrickhu(我是小F，向高手学习)

-- Date    :2009-09-03 17:49:35

-- Verstion:

--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 

--	Nov 24 2008 13:01:59 

--	Copyright (c) 1988-2005 Microsoft Corporation

--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

--

----------------------------------------------------------------

--> 测试数据：[tb]

if object_id('[tb]') is not null drop table [tb]

go 

create table [tb]([id] int,[name] varchar(8))

insert [tb]

select 1,'N1,N2,N3'

--------------开始查询--------------------------

declare @str varchar(8000) 



set @str = 'N1,N2,N3'



set @str =  'select  name='''+replace(@str,',',''''+' union all select ''')+'''' 



exec(@str)





select 

    a.id,b.name

from 

    (select id,name=convert(xml,'<root><v>'+replace(name,',','</v><v>')+'</v></root>') from Tb)a

outer apply

    (select name=C.v.value('.','nvarchar(100)') from a.name.nodes('/root/v')C(v))b



----------------结果----------------------------

/* id          name

----------- ----------------------------------------------------------------------------------------------------

1           N1

1           N2

1           N3



(3 行受影响)



*/

soft_wsx 2009-09-03

打赏
举报

这是分拆呀！

--小F-- 2009-09-03

打赏
举报

----------------------------------------------------------------

-- Author  :fredrickhu(我是小F，向高手学习)

-- Date    :2009-09-03 17:49:35

-- Verstion:

--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 

--	Nov 24 2008 13:01:59 

--	Copyright (c) 1988-2005 Microsoft Corporation

--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

--

----------------------------------------------------------------

--> 测试数据：[tb]

if object_id('[tb]') is not null drop table [tb]

go 

create table [tb]([id] int,[name] varchar(8))

insert [tb]

select 1,'N1,N2,N3'

--------------开始查询--------------------------

declare @str varchar(8000) 



set @str = 'N1,N2,N3'



set @str =  'select  name='''+replace(@str,',',''''+' union all select ''')+'''' 



exec(@str)

----------------结果----------------------------

/* name

----

N1

N2

N3



(3 行受影响)



*/

soft_wsx 2009-09-03

打赏
举报

等一下！

华夏小卒 2009-09-03

打赏
举报

是列转行

不是行专列

csdyyr 2009-09-03

打赏
举报

拆分表:



--> --> (Roy)生成測試數據

 

if not object_id('Tab') is null

    drop table Tab

Go

Create table Tab([Col1] int,[COl2] nvarchar(5))

Insert Tab

select 1,N'a,b,c' union all

select 2,N'd,e' union all

select 3,N'f'

Go



SQL2000用辅助表:

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.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID) 

from 

    Tab a,#Num b

where

    charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=','





SQL2005用Xml:



select 

    a.COl1,b.Col2

from 

    (select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from Tab)a

outer apply

    (select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b









SQL05用CTE:



;with roy as 

(select Col1,COl2=cast(left(Col2,charindex(',',Col2+',')-1) as nvarchar(100)),Split=cast(stuff(COl2+',',1,charindex(',',Col2+','),'') as nvarchar(100)) from Tab

union all

select Col1,COl2=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from Roy where split>''

)

select COl1,COl2 from roy order by COl1 option (MAXRECURSION 0)



生成结果:

/*

Col1        COl2

----------- -----

1           a

1           b

1           c

2           d

2           e

3           f

*/