求一表值函数

Question

uid  list             tid
A    |1|8|2|          00
B    |1|3|2|          tt
A    |1|9|4|          ii返回A    1          00
A    8          00
A    2          00
A    1          00
A    9          00
A    4          00
b

Answer 1

拆分吧?/*
标题：简单数据拆分(version 2.0)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2010-05-07
地点：重庆航天职业学院
描述：有表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)create table tb(id int,value varchar(30))
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
go--方法1.使用临时表完成
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b SELECT A.id, value = SUBSTRING(A.[value], B.id, CHARINDEX(',', A.[value] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[value], B.id, 1) = ','DROP TABLE #--方法2.如果数据量小，可不使用临时表
select a.id , value = substring(a.value , b.number , charindex(',' , a.value + ',' , b.number) - b.number) 
from tb a join master..spt_values  b 
on b.type='p' and b.number between 1 and len(a.value)
where 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--方法1.使用xml完成
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--方法2.使用CTE完成
;with tt as 
(select id,[value]=cast(left([value],charindex(',',[value]+',')-1) as nvarchar(100)),Split=cast(stuff([value]+',',1,charindex(',',[value]+','),'') as nvarchar(100)) from tb
union all
select id,[value]=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from tt where split>''
)
select id,[value] from tt order by id option (MAXRECURSION 0)
DROP TABLE tb/*
id          value
----------- ------------------------------
1           aa
1           bb
2           aaa
2           bbb
2           ccc(5 行受影响)
*/

Answer 2

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-11-14 17:07:38
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
-- Apr 22 2011 11:57:00 
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([uid] varchar(1),[list] varchar(7),[tid] varchar(2))
insert [tb]
select 'A','|1|8|2|','00' union all
select 'B','|1|3|2|','tt' union all
select 'A','|1|9|4|','ii'
--------------开始查询--------------------------
Select
    a.uid,COl2=substring(a.list,b.number,charindex('|',a.list+'|',b.number)-b.number),tid
from 
    Tb a join master..spt_values  b 
    ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.list)
where
     substring('|'+a.list,b.number,1)='|'
----------------结果----------------------------
/* uid  COl2    tid
---- ------- ----
A            00
A    1       00
A    8       00
A    2       00
B            tt
B    1       tt
B    3       tt
B    2       tt
A            ii
A    1       ii
A    9       ii
A    4       ii(12 行受影响)*/

Answer 3

create table tb(uid char(1),list varchar(20),tid varchar(10))
insert into tb
select 'A','|1|8|2|','oo' union
select 'B','|1|3|2|','tt' union
select 'A','|1|9|4|','ii'select a.uid,
    list=substring(a.list,b.number,charindex('|',a.list,b.number)-b.number),
     a.tid
from tb a,master..spt_values b
where b.[type] = 'p' and b.number between 2 and len(a.list)
    and substring('|'+a.list,b.number,1) = '|'/*
uid  list                 tid
---- -------------------- ----------
A    1                    oo
A    8                    oo
A    2                    oo
A    1                    ii
A    9                    ii
A    4                    ii
B    1                    tt
B    3                    tt
B    2                    tt(9 行受影响)