一列转多行

Question

表Tb 有一列 b格式如下:
aID    b
1    1,2,
2    2,3,5
3    6,如果得到
a    b
1    1
1    2
2    2
2    3
2    5
3    6

Answer 1

/*
标题：分拆列值
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-11-20
地点：广东深圳
描述有表tb, 如下:
id          value
----------- -----------
1           aa,bb
2           aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id          value
----------- --------
1           aa
1           bb
2           aaa
2           bbb
2           ccc
*/--1. 旧的解决方法(sql server 2000)
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b SELECT A.id, SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[values], B.id, 1) = ','DROP TABLE #--2. 新的解决方法(sql server 2005) 
create table tb(id int,value varchar(30))
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
go
SELECT A.id, B.value
FROM(
    SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') FROM tb
)A
OUTER APPLY(
    SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v)
)BDROP TABLE tb/*
id          value
----------- ------------------------------
1           aa
1           bb
2           aaa
2           bbb
2           ccc(5 行受影响)
*/

Answer 2

create table tab1 (aID INT,b VARCHAR(10))
INSERT INTO tab1 values(1,'1,2')
insert into tab1 values(2,'2,3,5')
insert into tab1 values(3,'6')
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b SELECT A.aID, SUBSTRING(A.b, B.id, CHARINDEX(',', A.b + ',', B.id) - B.id)
FROM tab1 A, # B
WHERE SUBSTRING(',' + A.b, B.id, 1) = ','
/*
aID         
----------- ----------
1           1
1           2
2           2
2           3
2           5(5 行受影响)*/

Answer 3

--
IF OBJECT_ID('dbo.Nums') IS NOT NULL
  DROP TABLE dbo.Nums;
GO
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @max AS INT, @rc AS INT;
SET @max = 1000000;
SET @rc = 1;INSERT INTO Nums VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
  INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;
  SET @rc = @rc * 2;
ENDINSERT INTO dbo.Nums
  SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;
GOSELECT aID ,
ROW_NUMBER() OVER (PARTITION BY aID ORDER BY dbo.TB.aID) AS pos,
CAST(SUBSTRING(b,n,CHARINDEX(',',b+',',n) -n) AS INT ) AS element
FROM dbo.TB
JOIN dbo.nums
ON
n<=LEN(dbo.TB.b) AND SUBSTRING(','+b,n,1) = ','/*
result:
aID        pos                  element
---------- -------------------- -----------
1          1                    1
1          2                    2
2          1                    2
2          2                    3
2          3                    5
3          1                    6(6 行受影响)
*/

Answer 4

--建立测试环境
set nocount on
create table test(aid varchar(20),b varchar(20))
insert into test select '1','1,2'
insert into test select '2','2,3,5'
insert into test select '3','6'
go
--测试
declare @s varchar(8000)
set @s='select left(a,charindex('':'',a)-1) as aid,right(a,len(a)-charindex('':'',a)) as b from('
select @s=@s+'select '''+replace(b,',',':'+aid+''' as a union select ''')+':'+aid+''' union ' from test
set @s=left(@s,len(@s)-6)+')a'exec(@s)
--删除测试环境
drop table test
 set nocount off

Answer 5

create table tab1 (aID INT,b VARCHAR(10))
INSERT INTO tab1 values(1,'1,2')
insert into tab1 values(2,'2,3,5')
insert into tab1 values(3,'6')
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b SELECT A.aID, SUBSTRING(A.b, B.id, CHARINDEX(',', A.b + ',', B.id) - B.id)
FROM tab1 A, # B
WHERE SUBSTRING(',' + A.b, B.id, 1) = ','
/*
aID         
----------- ----------
1           1
1           2
2           2
2           3
2           5
3           6
(6 行受影响)*/

Answer 6

分拆列值 有表tb, 如下: 
id          value 
----------- ----------- 
1          aa,bb 
2          aaa,bbb,ccc 
欲按id,分拆value列, 分拆后结果如下: 
id          value 
----------- -------- 
1          aa 
1          bb 
2          aaa 
2          bbb 
2          ccc 1. 旧的解决方法(sql server 2000) 
 
select a.id, substring(a.[value], b.number, charindex(',', a.[value] + ',', b.number) - b.number) 
FROM tb a, master..spt_values  b 
WHERE b.type='p' and substring(',' + a.[value],b.number, 1) = ',' 
2. 新的解决方法(sql server 2005) create table tb(id int,value varchar(30)) 
insert into tb values(1,'aa,bb') 
insert into tb values(2,'aaa,bbb,ccc') 
go 
SELECT a.id, b.value 
FROM( 
    SELECT id, [value] = CONVERT(xml,' <root> <v>' + REPLaCE([value], ',', ' </v> <v>') + ' </v> </root>') FROM tb 
)a 
OUTER aPPLY( 
    SELECT value = N.v.value('.', 'varchar(100)') FROM a.[value].nodes('/root/v') N(v) 
)b DROP TabLE tb /* 
id          value 
----------- ------------------------------ 
1          aa 
1          bb 
2          aaa 
2          bbb 
2          ccc (5 行受影响) 
*/

Answer 7

--> --> (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'
GoSQL2000用辅助表:
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)

Answer 8

--一般都会有分字符的函数吧，这里贴个
CREATE FUNCTION [dbo].[f_splitToTable] (@p_StringList varchar(max),@flag char(1)=',')  
returns @t TABLE (id int identity(1,1),s VARCHAR(20))  
AS  
BEGIN  
DECLARE @ib int,@ie int  
set @p_StringList=ltrim(rtrim(@p_StringList))  
if left(@p_StringList,1)=@flag set @p_StringList=right(@p_StringList,len(@p_StringList)-1)  
if right(@p_StringList,1)<>@flag set @p_StringList=@p_StringList+@flag  
select @ib=0,@ie=0  
select @ie=charindex(@flag,@p_StringList,@ib+1)  
WHILE @ie > 0  
BEGIN  
insert into @t select substring(@p_StringList,@ib+1,@ie-@ib-1)  
select @ib=@ie,@ie=charindex(@flag,@p_StringList,@ib+1)  
END   
RETURN  
END--查询
select a.aid,b.s
from test a outer apply dbo.f_splitToTable(a.b,',') b

Answer 9

create table tab1 (aID INT,b VARCHAR(10))
INSERT INTO tab1 values(1,'1,2')
insert into tab1 values(2,'2,3,5')
insert into tab1 values(3,'6')CREATE FUNCTION dbo.f_str(@id varchar(20))
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @r varchar(8000)
    SET @r = ''
    SELECT @r =@r +','+b FROM tab1
    WHERE aID=@id
    RETURN STUFF(@r, 1, 1, '')
ENDselect distinct(aID),dbo.f_str(aID) from tab1aID         
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           1,2
2           2,3,5
3           6(3 行受影响)

Answer 10

不好意思
看错啦SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b SELECT A.aID, SUBSTRING(A.b, B.id, CHARINDEX(',', A.b + ',', B.id) - B.id)
FROM tab1 A, # B
WHERE SUBSTRING(',' + A.b, B.id, 1) = ','

Answer 11

create table tab1 (aID INT,b VARCHAR(10))
INSERT INTO tab1 values(1,'1,2')
insert into tab1 values(2,'2,3,5')
insert into tab1 values(3,'6')SELECT A.aID, SUBSTRING(A.b, B.number, CHARINDEX(',', A.b + ',', B.number) - B.number)
FROM tab1 A, master..spt_values  B  
WHERE SUBSTRING(',' + A.b, B.number, 1) = ',' and B.type='p'drop table tab1

Answer 12

hehe,老问题啊，这个实现方法太多了啊！