某表有字段A和B:格式如下字段A 字段B
a001 001,写的好的|101,这也太乱了|
a123 001,卷面不干净|
a234 001,不错|102,很好|103,这也太乱了|求一方法,最好使用自定义函数得到结果字段A 字段B 字段C
a001 001 写得好的
a001 101 这也太乱了
a123 001 卷面不干净
a234 001 不错
a234 102 很好
a234 103 这也太乱了
a001 001,写的好的|101,这也太乱了|
a123 001,卷面不干净|
a234 001,不错|102,很好|103,这也太乱了|求一方法,最好使用自定义函数得到结果字段A 字段B 字段C
a001 001 写得好的
a001 101 这也太乱了
a123 001 卷面不干净
a234 001 不错
a234 102 很好
a234 103 这也太乱了
RETURNS TABLE
AS
RETURN(
SELECT
A.col2,
LEFT(A.col2,CHARINDEX(',',A.col2)-1) AS col2,
RIGHT(A.col2,CHARINDEX(',',REVERSE(A.col2))-1) AS col3
FROM (
SELECT
A.col1,
SUBSTRING(A.col2,B.number,CHARINDEX(@Split,A.col2+@Split,B.number)-B.number) AS col2
FROM tb AS A
JOIN master.dbo.spt_values AS B
ON B.type='p' AND B.number BETWEEN 1 AND LEN(B.col2)
AND SUBSTRING(@Split,B.number,1) = @Split
) AS T
);
/*
问题:如果上述两表互相换一下:即表结构和数据为:
姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94
想变成(得到如下结果):
姓名 课程 分数
---- ---- ----
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
--------------
*/create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go--SQL SERVER 2000 静态SQL。
select * from
(
select 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns
where name! = N'姓名' and ID = object_id('tb') --表名tb,不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t--SQL SERVER 2005 动态SQL,同SQL SERVER 2000 动态SQL。
--> 测试数据: [TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB] (A varchar(4),B varchar(2000))
insert into [TB]
select 'a001','001,写的好的|101,这也太乱了|' union all
select 'a123','001,卷面不干净|'l union all
select 'a234','001,不错|102,很好|103,这也太乱了|'gocreate function fn_Tb(@a varchar(200))
returns @t table(A varchar(200),B varchar(800),C varchar(800))
as
begin
declare @B varchar(2000), @D varchar(200)
select @B=B from TB where A=@a
while charindex('|',@B)>0
begin
set @D = left(@B,charindex('|',@B)-1)
set @B=stuff(@B,1,charindex('|',@B),'')
insert into @t select @a,left(@D,charindex(',',@D)-1),right(@D,len(@D)-charindex(',',@D))
end
return
endselect * from fn_Tb('a234')
/*
a234 001 不错
a234 102 很好
a234 103 这也太乱了
*/select b.*
from TB as a
outer apply (select * from fn_Tb(a.A))as b
/*
a001 001 写的好的
a001 101 这也太乱了
a123 001 卷面不干净
a234 001 不错
a234 102 很好
a234 103 这也太乱了
*/
-- Author: liangCK 小梁 & angellan 兰儿
-- Date : 2008-10-24 09:46:00
------------------------------------------------
--> 生成测试数据: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
CREATE TABLE [tb] (col1 VARCHAR(4),col2 VARCHAR(33))
INSERT INTO [tb]
SELECT 'a001','001,写的好的|101,这也太乱了|' UNION ALL
SELECT 'a123','001,卷面不干净|' UNION ALL
SELECT 'a234','001,不错|102,很好|103,这也太乱了|'--SQL查询如下:
GO
CREATE FUNCTION dbo.f_Split_tb(@Split VARCHAR(1))
RETURNS TABLE
AS
RETURN(
SELECT
col1,
LEFT(col2,CHARINDEX(',',col2)-1) AS col2,
RIGHT(col2,CHARINDEX(',',REVERSE(col2))-1) AS col3
FROM (
SELECT
A.col1,
SUBSTRING(A.col2,B.number,CHARINDEX(@Split,A.col2+@Split,B.number)-B.number) AS col2
FROM tb AS A
JOIN master.dbo.spt_values AS B
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.col2)
AND SUBSTRING(@Split+A.col2,B.number,1) = @Split
) AS T
);
GOSELECT * FROM dbo.f_Split_tb('|')GO
DROP TABLE tb
DROP FUNCTION dbo.f_Split_tb/*
col1 col2 col3
---- --------------------------------- ---------------------------------
a001 001 写的好的
a001 101 这也太乱了
a123 001 卷面不干净
a234 001 不错
a234 102 很好
a234 103 这也太乱了(6 行受影响)*/
split('表名','|');
否则的话,
你的函数里面就必须使用动态SQL,
而函数里面不能使用动态SQL.
所以,这条路是不通的哈.,
函数不能用动态SQL..改用存储过程吧.
有表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 行受影响)
*/
a123 001 张三 101 李四 001,写的好的|101,这也太乱了|
a124 001 张三 001,卷面不干净|想得到这样的结果
a123 张三 写的好的
a123 李四 这也太乱了
a124 张三 卷面不干净 把上面的如何改呢
a123 001 张三 101 李四 001,写的好的|101,这也太乱了
a124 001 张三 001,卷面不干净| 想得到这样的结果
a123 张三 写的好的
a123 李四 这也太乱了
a124 张三 卷面不干净 把上面的又如何改呢
declare @t table (col varchar(50))
insert into @t
select 'aa,bb,bb' union all
select 'AAA,BBB' union all
select 'AAA'
drop table #t
declare @i int,@s varchar(1000)
set @i=0
select col into #t from @t
while @@rowcount>0
begin
set @i=@i+1
set @s='alter table #t add col'+cast(@i as varchar)+' varchar(100)'
exec(@s)
set @s='update #t set col'+cast(@i as varchar)+'=left(col,charindex('','',col+'','')-1),
col=stuff(col,1,charindex('','',col+'',''),'''')
where col>'''''
print @s
exec(@s)
end
select * from #t
INSERT @a SELECT 'a123','001','张三','101','李四',null,null,null,null,'001,写的好的|101,这也太乱了|'
union all select 'a124','001','张三',null ,null ,null,null,null,null,'001,卷面不干净|'
SELECT 编号,教师编号,教师名,标记,
SUBSTRING(标记, CHARINDEX(教师编号,标记)+len(教师编号+','),charindex('|',标记,charindex(教师编号,标记)+1)-CHARINDEX(教师编号,标记)-len(教师编号+',')) 名称
FROM
(
SELECT 编号,教师编号1 教师编号,教师名1 教师名,标记 from @a
UNION ALL
SELECT 编号,教师编号2,教师名2,标记 from @a
UNION ALL
SELECT 编号,教师编号3,教师名3,标记 from @a
UNION ALL
SELECT 编号,教师编号4,教师名4,标记 from @a
)aa
WHERE 教师编号 is not null
ORDER BY 编号
/*
编号 教师编号 教师名 标记 名称
-------------------- -------------------- -------------------- ------------------------------ ------------------------------
a123 001 张三 001,写的好的|101,这也太乱了| 写的好的
a123 101 李四 001,写的好的|101,这也太乱了| 这也太乱了
a124 001 张三 001,卷面不干净| 卷面不干净(所影响的行数为 3 行)
*/
-- Author: Locket
-- Date: 2008/12/16
-- Func: 傳入字符串,按照分隔符返回結果集
-- Sample: Select * From dbo.fnSplit('1234*+*567*+*123*+*','*+*')
-- ============================================================ALTER FUNCTION [dbo].[fnSplit]
(
@Input nvarchar(4000),
@Split nvarchar(5)
)
Returns @SplitTable Table(ID int,Item nvarchar(4000))
AS
Begin
Declare @nLoc smallint --獲得分隔的位置
Declare @nLen smallint --整個字串的長度
Declare @cItem nvarchar(4000) --查詢出來的某個詞
Declare @cTmp nvarchar(4000) --臨時存儲的數據
Declare @nCount smallint --第幾個
Set @nCount = 0
Set @cTmp = @Input
Set @cItem = @Input
Set @nLen = Len(@cTmp)
--獲得分隔符位置
Set @nLoc = CharIndex(@Split,@cTmp)
While @nLoc > 0
Begin
--得到一個item數據
Set @cItem = SubString(@cTmp,1,@nLoc - 1)
--重新設置臨時字串數據
Set @cTmp = SubString(@cTmp,@nLoc + Len(@Split),@nLen - @nLoc - Len(@Split) + 1)
Set @nLen = Len(@cTmp)
--插入數據到臨時表
Insert Into @SplitTable(ID,Item) Values(@nCount,@cItem)
Set @nCount = @nCount + 1
--獲得分隔符位置
Set @nLoc = CharIndex(@Split,@cTmp)
END
If @nCount > 0 Or @nLen > 0
Insert Into @SplitTable(ID,Item) Values(@nCount,@cTmp)
Return
End
-- ============================================================
-- Author: Locket
-- Date: 2008/12/16
-- Func: 傳入字符串,按照分隔符返回第幾個Item,如果為空串則返回默認值
-- Sample: Declare @Item nvarchar(4000)
-- Set @Item = dbo.fnSplitByIndex('123&&456&&789&&','&&',3,'')
-- Select @Item
-- ============================================================ALTER FUNCTION [dbo].[fnSplitByIndex]
(
@Input nvarchar(4000),
@Split nvarchar(5),
@Index smallint,
@DefaultValue nvarchar(4000)
)
Returns nvarchar(4000)
AS
Begin
Declare @nLoc smallint --獲得分隔的位置
Declare @nLen smallint --整個字串的長度
Declare @cItem nvarchar(4000) --查詢出來的某個詞
Declare @cTmp nvarchar(4000) --臨時存儲的數據
Declare @nCount smallint --第幾個
Set @nCount = 0
Set @cTmp = @Input
Set @cItem = @Input
Set @nLen = Len(@cTmp)
--獲得分隔符位置
Set @nLoc = CharIndex(@Split,@cTmp) While @nLoc > 0
Begin
--得到一個item數據
Set @cItem = SubString(@cTmp,1,@nLoc - 1)
--重新設置臨時字串數據
Set @cTmp = SubString(@cTmp,@nLoc + Len(@Split),@nLen - @nLoc - Len(@Split) + 1)
Set @nLen = Len(@cTmp)
--判斷:如果是需要的那個索引數據,則返回
If @nCount = @Index
Begin
If LTrim(RTrim(@cItem)) = '' Set @cItem = @DefaultValue
Return @cItem
End
Set @nCount = @nCount + 1
--獲得分隔符位置
Set @nLoc = CharIndex(@Split,@cTmp)
END
--處理最後一筆資料
If @cTmp <> '' And @nCount = @Index
Set @DefaultValue = @cTmp
--返回最後的數據
Return @DefaultValue
End