某表有字段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    这也太乱了    

解决方案 »

  1.   

    sql 2000 or sql 2005 ?
      

  2.   

    CREATE FUNCTION dbo.f_Split_tb(@Split VARCHAR(1))
        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
        );
      

  3.   

    行转列,参考:
    /*
    问题:如果上述两表互相换一下:即表结构和数据为:
    姓名 语文 数学 物理
    张三 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。
      

  4.   


    --> 测试数据: [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 这也太乱了
    */
      

  5.   

    楼主需要说下你的where条件,以确定在sql2000里如何写函数的传入参数.
      

  6.   

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

  7.   

    小艾,将你的那个输入的表名改为参数怎么写
    split('表名','|');
      

  8.   

    表明不能作为参数传入吧,
    否则的话,
    你的函数里面就必须使用动态SQL,
    而函数里面不能使用动态SQL.
    所以,这条路是不通的哈.,
      

  9.   


    函数不能用动态SQL..改用存储过程吧.
      

  10.   

    我已经改为存储过程了,请问如何能够在DOS下使用批处理执行这个存储过程呢
      

  11.   


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

  12.   

    to 小艾:我现在的表结构是这样的编号  教师编号 教师名  教师编号 教师名   标记 
    a123  001      张三    101     李四    001,写的好的|101,这也太乱了|
    a124  001     张三                     001,卷面不干净|想得到这样的结果
    a123  张三    写的好的
    a123  李四    这也太乱了
    a124  张三    卷面不干净 把上面的如何改呢
      

  13.   

    to 小艾: 我现在的表结构是这样的 编号  教师编号1 教师名1  教师编号2 教师名2 教师编号3 教师名3 教师编号4 教师名4 标记 
    a123  001      张三    101    李四                                          001,写的好的|101,这也太乱了 
    a124  001    张三                                                            001,卷面不干净| 想得到这样的结果 
    a123  张三    写的好的 
    a123  李四    这也太乱了 
    a124  张三    卷面不干净 把上面的又如何改呢
      

  14.   

    分拆数据到列
    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
      

  15.   

    declare @a table(编号 varchar(20),  教师编号1 varchar(20), 教师名1 varchar(20),  教师编号2 varchar(20), 教师名2 varchar(20), 教师编号3 varchar(20), 教师名3 varchar(20), 教师编号4 varchar(20), 教师名4 varchar(20), 标记 varchar(100))
    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 行)
    */
      

  16.   

    兩個處理字符串的函數,基本可以解決問題-- ============================================================
    -- 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