急求一SQL

[jastion]

有資料如下
A 1,2
B 1,2,9
C 5,6
想得到
A 1
A 2
B 1
B 2
B 9
C 5
C 6

[htl258_Tony]

字符串拆分，留给小梁吧

[sdhdy]

分拆列值 

有表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 #temp FROM syscolumns a, syscolumns b 

SELECT A.id, SUBSTRING(A.[value], B.id, CHARINDEX(',', A.[value] + ',', B.id) - B.id) 
FROM tb A, #temp B 
WHERE SUBSTRING(',' + A.[value], B.id, 1) = ',' 

DROP TABLE #temp 

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 行受影响) 
*/

[ws_hgo]

create TABLE TT (id varchar(32),type varchar(32),billMoney decimal(12,2))
INSERT INTO TT
        SELECT '10001','支票',2000
UNION ALL 
       SELECT '10001','预留款',1000
UNION ALL 
        SELECT '10001','现金',800
UNION ALL 
       SELECT '10002','挂账',1000
UNION ALL 
        SELECT '10002','现金',800

create function dbo.FC_Str(@id int)
returns varchar(1000)
as
begin
  declare @str varchar(1000)
  set @str='' 
  select @str=@str+','+type+':'+cast(billMoney as varchar) from TT where id=@id
  return stuff(@str,1,1,'')
end

select id,sum(billMoney) payedMoney,dbo.FC_Str(id) from TT group by id

[ws_hgo]

我的BLOG:http://blog.csdn.net/ws_hgo/archive/2009/03/17/3999390.aspx

[liangCK]

[Quote=引用 1 楼 htl258 的回复:]
字符串拆分，留给小梁吧
[/Quote]

偶做腻了。

[ws_hgo]

[Quote=引用 5 楼 liangCK 的回复:]
引用 1 楼 htl258 的回复:
字符串拆分，留给小梁吧


偶做腻了。
[/Quote]

[htl258_Tony]

[Quote=引用 5 楼 liangCK 的回复:]
引用 1 楼 htl258 的回复:
字符串拆分，留给小梁吧


偶做腻了。
[/Quote]
那我们学着你试试了。

[jinjazz]

P梁又开始在我任务栏上不断的冉冉升起了，你就不能换个浏览器哈，比如maxthon或者firefox

[htl258_Tony]

declare @t table(a varchar(10),b varchar(20))
insert @t select 'A',  '1,2' 
union all select 'B', '1,2,9' 
union all select 'C', '5,6'


SELECT a.a, b=SUBSTRING(a.b, B.number, CHARINDEX(',', a.b + ',', B.number) - B.number) 
FROM @t a,master..spt_values B 
WHERE b.number>0 and b.type='p' and SUBSTRING(',' + a.b, b.number, 1) = ',' 

a          b                    
---------- -------------------- 
A          1
A          2
B          1
B          2
B          9
C          5
C          6

（所影响的行数为 7 行）

[ws_hgo]

create function FC_SlpitStr(@Str nvarchar(4000),@Split nvarchar(100))
returns @R table (Col nvarchar(100))
as
begin
    declare @StrLen int
    set @StrLen=len(@Str)
    while charindex(@Split,@StrLen)>0
     begin
      insert into @R values(left(@Str,charindex(@Split,@StrLen)-1))
       set @Str=stuff(@Str,1,charindex(@Split,@StrLen),'')
      end
     insert into @R values(@Str)
return 
end 

declare @a nvarchar(4000)
set @a='1,23,a'
select dbo.FC_SlpitStr(@a,',')
--(2)动态T-sql语句
declare @S varchar(100)
set @s='1,23,a'

declare @sql varchar(100)
set @sql='select col='''+replace(@S, ',' , ''' union all select ''')+''''
print(@sql)

[LIHY70]

学习~~~

[dawugui]

/*
标题：分拆列值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)
)B

DROP TABLE tb

/*
id          value
----------- ------------------------------
1           aa
1           bb
2           aaa
2           bbb
2           ccc

(5 行受影响)
*/

[肥龙上天]

create table #1(a varchar(10),b varchar(20))
insert #1 select 'A',  '1,2' 
union all select 'B', '1,2,9' 
union all select 'C', '5,6'



select t.a,tt.value 
from 
(
	select a,cast('<r>'+replace(b,',','</r><r>')+'</r>' as xml) as value from #1
) T
outer apply
(
	select value = N.V.value('.','varchar(100)') from T.[value].nodes('/r') N(V)
)
TT
/*a          value
---------- ----------------------------------------------------------------------------------------------------
A          1
A          2
B          1
B          2
B          9
C          5
C          6

(7 row(s) affected)*/