ORACLE 行列转换通用存储过程，已经转换的MSSQL版本

下面是ORACLE行列转换通用存储过程：

1、

Function Fun_Split_Str(p_Str Varchar2, p_Division Varchar2, p_Seq Int)
    Return Varchar2 Is
    v_First Int;
    v_Last  Int;
  Begin
    If p_Seq < 1 Then
      Return Null;
    End If;
    If p_Seq = 1 Then
      If Instr(p_Str, p_Division, 1, p_Seq) = 0 Then
        Return p_Str;
      Else
        Return Substr(p_Str, 1, Instr(p_Str, p_Division, 1) - 1);
      End If;
    Else
      v_First := Instr(p_Str, p_Division, 1, p_Seq - 1);
      v_Last  := Instr(p_Str, p_Division, 1, p_Seq);
      If (v_Last = 0) Then
        If (v_First > 0) Then
          Return Substr(p_Str, v_First + 1);
        Else
          Return Null;
        End If;
      Else
        Return Substr(p_Str, v_First + 1, v_Last - v_First - 1);
      End If;
    End If;
  End Fun_Split_Str;

 

 

 

 

 

 

  /*
  */
  Procedure Pro_Rows_Column(p_Table      In Varchar2,
                            p_Keep_Cols  In Varchar2,
                            p_Pivot_Cols In Varchar2,
                            p_Where      In Varchar2 Default Null,
                            p_Refc       In Out Refc) Is
    v_Sql Varchar2(4000);
    Type v_Keep_Ind_By Is Table Of Varchar2(4000) Index By Binary_Integer;
    v_Keep v_Keep_Ind_By;
    Type v_Pivot_Ind_By Is Table Of Varchar2(4000) Index By Binary_Integer;
    v_Pivot      v_Pivot_Ind_By;
    v_Keep_Cnt   Int;
    v_Pivot_Cnt  Int;
    v_Max_Cols   Int;
    v_Partition  Varchar2(4000);
    v_Partition1 Varchar2(4000);
    v_Partition2 Varchar2(4000);
  Begin
    v_Keep_Cnt  := Length(p_Keep_Cols) - Length(Replace(p_Keep_Cols, ',')) + 1;
    v_Pivot_Cnt := Length(p_Pivot_Cols) -
                   Length(Replace(p_Pivot_Cols, ',')) + 1;
    For i In 1 .. v_Keep_Cnt Loop
      v_Keep(i) := Fun_Split_Str(p_Keep_Cols, ',', i);
    End Loop;
    For j In 1 .. v_Pivot_Cnt Loop
      v_Pivot(j) := Fun_Split_Str(p_Pivot_Cols, ',', j);
    End Loop;
    v_Sql := 'select max(count(*)) from ' || p_Table || ' group by ';
    For i In 1 .. v_Keep.Last Loop
      v_Sql := v_Sql || v_Keep(i) || ',';
    End Loop;
    v_Sql := Rtrim(v_Sql, ',');
    Execute Immediate v_Sql
      Into v_Max_Cols;
    v_Partition := 'select ';
    For x In 1 .. v_Keep.Count Loop
      v_Partition1 := v_Partition1 || v_Keep(x) || ',';
    End Loop;
    For y In 1 .. v_Pivot.Count Loop
      v_Partition2 := v_Partition2 || v_Pivot(y) || ',';
    End Loop;
    v_Partition1 := Rtrim(v_Partition1, ',');
    v_Partition2 := Rtrim(v_Partition2, ',');
    v_Partition  := v_Partition || v_Partition1 || ',' || v_Partition2 ||
                    ', row_number() over (partition by ' || v_Partition1 ||
                    ' order by ' || v_Partition2 || ') rn from ' || p_Table;
    v_Partition  := Rtrim(v_Partition, ',');
    v_Sql        := 'select ';
    For i In 1 .. v_Keep.Count Loop
      v_Sql := v_Sql || v_Keep(i) || ',';
    End Loop;
    For i In 1 .. v_Max_Cols Loop
      For j In 1 .. v_Pivot.Count Loop
        v_Sql := v_Sql || ' max(decode(rn,' || i || ',' || v_Pivot(j) ||
                 ',null))' || v_Pivot(j) || '_' || i || ',';
      End Loop;
    End Loop;
    If p_Where Is Not Null Then
      v_Sql := Rtrim(v_Sql, ',') || ' from (' || v_Partition || ' ' ||
               p_Where || ') group by ';
    Else
      v_Sql := Rtrim(v_Sql, ',') || ' from (' || v_Partition ||
               ') group by ';
    End If;
    For i In 1 .. v_Keep.Count Loop
      v_Sql := v_Sql || v_Keep(i) || ',';
    End Loop;
    v_Sql := Rtrim(v_Sql, ',');
    Pro_Print_Sql(v_Sql);
    Open p_Refc For v_Sql;
  Exception
    When Others Then
      Open p_Refc For
        Select 'x' From Dual Where 0 = 1;
  End Pro_Rows_Column;

 

 

  /*
  */
  Procedure Pro_Rows_Column_Real(p_Table       In Varchar2,
                                 p_Keep_Cols   In Varchar2, --固定列，多列以“,”分开
                                 p_Pivot_Col   In Varchar2, --指定的行值转化为列名称
                                 p_Pivot_Index In Varchar2, --指定的行值转化为列名称的排序列名称
                                 p_Pivot_Val   In Varchar2, --列值
                                 p_Where       In Varchar2 Default Null, --查询条件
                                 p_Refc        In Out Refc) Is
    v_Sql Varchar2(4000);
    Type v_Keep_Ind_By Is Table Of Varchar2(4000) Index By Binary_Integer;
    v_Keep v_Keep_Ind_By;
    Type v_Pivot_Ind_By Is Table Of Varchar2(4000) Index By Binary_Integer;
    v_Pivot       v_Pivot_Ind_By;
    v_Keep_Cnt    Int;
    v_Group_By    Varchar2(2000);
    v_Pivot_Index Varchar2(100);
  Begin
    If p_Pivot_Index Is Null Then
      v_Pivot_Index := p_Pivot_Col;
    Else
      v_Pivot_Index := p_Pivot_Index;
    End If;
    v_Keep_Cnt := Length(p_Keep_Cols) - Length(Replace(p_Keep_Cols, ',')) + 1;
    For i In 1 .. v_Keep_Cnt Loop
      v_Keep(i) := Fun_Split_Str(p_Keep_Cols, ',', i);
    End Loop;
    v_Sql := 'select ' || 'cast(' || p_Pivot_Col ||
             ' as varchar2(200)) as ' || p_Pivot_Col || ' from ' || p_Table ||
             ' group by ' || p_Pivot_Col || ',' || v_Pivot_Index ||
             ' Order By ' || v_Pivot_Index;
    Execute Immediate v_Sql Bulk Collect
      Into v_Pivot;
    For i In 1 .. v_Keep.Count Loop
      v_Group_By := v_Group_By || v_Keep(i) || ',';
    End Loop;
    v_Group_By := Rtrim(v_Group_By, ',');
    v_Sql      := 'select ' || v_Group_By || ',';
    For x In 1 .. v_Pivot.Count Loop
      If v_Pivot(x) Is Not Null Then
        v_Sql := v_Sql || ' max(decode(' || p_Pivot_Col || ',' || Chr(39) ||
                 v_Pivot(x) || Chr(39) || ',' || p_Pivot_Val ||
                 ',null)) as "' || v_Pivot(x) || '",';
      End If;
    End Loop;
    v_Sql := Rtrim(v_Sql, ',');
    If p_Where Is Not Null Then
      v_Sql := v_Sql || ' from ' || p_Table || ' where ' || p_Where ||
               ' group by ' || v_Group_By;
    Else
      v_Sql := v_Sql || ' from ' || p_Table || ' group by ' || v_Group_By;
    End If;
    --    Pro_Print_Sql(v_Sql);
    Open p_Refc For v_Sql;
  Exception
    When Others Then
      Open p_Refc For
        Select 'x' From Dual Where 0 = 1;
  End Pro_Rows_Column_Real;

 

 

 

 

由于开发要使用到MSSQL，我历时1天改成了MSSQL版本

 

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER Procedure [dbo].[Pro_Rows_Column]
        @p_Table       Varchar(50),
        @p_Keep_Cols   Varchar(1000),
        @p_Pivot_Cols  Varchar(1000),
        @p_Where       Varchar(1000)
--                            p_Refc       In Out Refc
AS
DECLARE @v_Sql Varchar(4000)
--    Type v_Keep_Ind_By Is Table Of Varchar2(4000) Index By Binary_Integer;
--    v_Keep v_Keep_Ind_By;
--    Type v_Pivot_Ind_By Is Table Of Varchar2(4000) Index By Binary_Integer;
--    DECLARE @v_Pivot      v_Pivot_Ind_By;
    DECLARE @v_Keep_Cnt   Int
    DECLARE @v_Pivot_Cnt  Int
    DECLARE @v_Max_Cols   Int
    DECLARE @v_Partition  Varchar(4000)
    DECLARE @v_Partition1 Varchar(4000)
    DECLARE @v_Partition2 Varchar(4000)
    DECLARE @i_INDEX  INT
    DECLARE @j_INDEX  INT
    DECLARE @k_INDEX  INT
    DECLARE @v_tempstr VARCHAR(1000)
    DECLARE @v_Error  INT
  Begin
    SET @v_Keep_Cnt  = Len(@p_Keep_Cols) - Len(Replace(@p_Keep_Cols, ',','')) + 1
    SET @v_Pivot_Cnt = Len(@p_Pivot_Cols) -Len(Replace(@p_Pivot_Cols, ',','')) + 1

     IF(@@error !=0)
        BEGIN
            SET @v_Error =  @@error
        END

--    For i In 1 .. v_Keep_Cnt Loop
--      v_Keep(i) := Fun_Split_Str(p_Keep_Cols, ',', i);
--    End Loop;
    SET @i_INDEX = 1
    CREATE TABLE #KEEPTABLE
   (
        v_index int,
        v_Keep VARCHAR(1000)
    )


    WHILE(@i_INDEX<=@v_Keep_Cnt)
    BEGIN
        INSERT INTO #KEEPTABLE VALUES(@i_INDEX,dbo.Fun_Split_Str(@p_Keep_Cols, ',', @i_INDEX))
        SET @i_INDEX=@i_INDEX+1
    END
  
    IF(@@error !=0)
        BEGIN
            SET @v_Error =  @@error
        END
       
--    For j In 1 .. v_Pivot_Cnt Loop
--      v_Pivot(j) := Fun_Split_Str(p_Pivot_Cols, ',', j);
--    End Loop;
    SET @j_INDEX =1
    CREATE TABLE #PIVOTTABLE
   (
        v_index int,
        v_Keep VARCHAR(1000)
    )

   

    WHILE(@j_INDEX<=@v_Pivot_Cnt)
    BEGIN
        INSERT INTO #PIVOTTABLE VALUES(@j_INDEX,dbo.Fun_Split_Str(@p_Pivot_Cols, ',', @j_INDEX))
        SET @j_INDEX=@j_INDEX+1
    END
       
     IF(@@error !=0)
        BEGIN
            SET @v_Error =  @@error
        END


    SET @v_Sql = 'select @v_Max_Cols=max(count(*)) from ' + @p_Table + ' group by '

--    For i In 1 .. v_Keep.Last Loop
--      v_Sql := v_Sql || v_Keep(i) || ',';
--    End Loop;
      SELECT @j_INDEX =COUNT(*) FROM #KEEPTABLE
      SET @i_INDEX=1
      WHILE(@i_INDEX<=@j_INDEX)
      BEGIN
        SELECT @v_tempstr =v_Keep FROM #KEEPTABLE WHERE v_index = @i_INDEX
        SET @v_Sql = @v_Sql + LTRIM(RTRIM(@v_tempstr)) + ','
        SET @i_INDEX=@i_INDEX+1
      END
   
         IF(@@error !=0)
        BEGIN
            SET @v_Error =  @@error
        END

--    v_Sql := Rtrim(v_Sql, ',');
    SET @v_Sql = Rtrim(@v_Sql)
    SET @v_Sql = SUBSTRING(@v_Sql,0,LEN(@v_Sql))

    Execute (@v_Sql)

    SET @v_Partition = 'select '

--    For x In 1 .. v_Keep.Count Loop
--      v_Partition1 := v_Partition1 || v_Keep(x) || ',';
--    End Loop;

    SELECT @j_INDEX =COUNT(*) FROM #KEEPTABLE
      SET @i_INDEX=1
      WHILE(@i_INDEX<=@j_INDEX)
      BEGIN
        SELECT @v_tempstr = v_Keep FROM #KEEPTABLE WHERE v_index = @i_INDEX
        SET @v_Partition1 = @v_Partition1 + LTRIM(RTRIM(@v_tempstr)) + ','
        SET @i_INDEX=@i_INDEX+1
      END

         IF(@@error !=0)
        BEGIN
            SET @v_Error =  @@error
        END

--    For y In 1 .. v_Pivot.Count Loop
--      v_Partition2 := v_Partition2 || v_Pivot(y) || ',';
--    End Loop;
      SELECT @j_INDEX =COUNT(*) FROM #PIVOTTABLE
      SET @i_INDEX=1
      WHILE(@i_INDEX<=@j_INDEX)
      BEGIN
        SELECT @v_tempstr = v_Keep FROM #PIVOTTABLE WHERE v_index = @i_INDEX
        SET @v_Partition2 = @v_Partition2 + LTRIM(RTRIM(@v_tempstr)) + ','
        SET @i_INDEX=@i_INDEX+1
      END

         IF(@@error !=0)
        BEGIN
            SET @v_Error =  @@error
        END

--    SET @v_Partition1 = Rtrim(@v_Partition1, ',')
--    SET @v_Partition2 = Rtrim(@v_Partition2, ',')
    SET @v_Partition1 = Rtrim(@v_Partition1)
    SET @v_Partition1 = SUBSTRING(@v_Partition1,0,LEN(@v_Partition1))
    SET @v_Partition2 = Rtrim(@v_Partition2)
    SET @v_Partition2 = SUBSTRING(@v_Partition2,0,LEN(@v_Partition2))

-- v_Partition  := v_Partition || v_Partition1 || ',' || v_Partition2 ||
--                    ', row_number() over (partition by ' || v_Partition1 ||
--                    ' order by ' || v_Partition2 || ') rn from ' || p_Table;

    SET @v_Partition  = @v_Partition + @v_Partition1 + ',' + @v_Partition2 +
                    ', row_number() over ('-- partition by ' + @v_Partition1 +
                     +' order by ' + @v_Partition2 + ') rn from ' +@p_Table


--    SET @v_Partition  = Rtrim(v_Partition, ',');
        SET @v_Partition  = Rtrim(@v_Partition)
        SET @v_Partition = SUBSTRING(@v_Partition,0,LEN(@v_Partition))
    SET @v_Sql        = 'select '

     IF(@@error !=0)
        BEGIN
            SET @v_Error =  @@error
        END

--    For i In 1 .. v_Keep.Count Loop
--      v_Sql := v_Sql || v_Keep(i) || ',';
--    End Loop;
      SELECT @j_INDEX =COUNT(*) FROM #KEEPTABLE
      SET @i_INDEX=1
      WHILE(@i_INDEX<=@j_INDEX)
      BEGIN
        SELECT @v_tempstr = v_Keep FROM #KEEPTABLE WHERE v_index = @i_INDEX
        SET @v_Sql = @v_Sql + LTRIM(RTRIM(@v_tempstr)) + ','
        SET @i_INDEX=@i_INDEX+1
      END

         IF(@@error !=0)
        BEGIN
            SET @v_Error =  @@error
        END

--    For i In 1 .. @v_Max_Cols Loop
--      For j In 1 .. v_Pivot.Count Loop
--        v_Sql := v_Sql || ' max(decode(rn,' || i || ',' || v_Pivot(j) ||
--                 ',null))' || v_Pivot(j) || '_' || i || ',';
--      End Loop;
--    End Loop;
      SET @i_INDEX=1
      WHILE(@i_INDEX<=@v_Max_Cols)
      BEGIN
        SET @j_INDEX=1
        SELECT @k_INDEX =COUNT(*) FROM #PIVOTTABLE
        WHILE(@j_INDEX<=@k_INDEX)
        BEGIN
            SELECT @v_tempstr = v_Keep FROM #PIVOTTABLE WHERE v_index = @j_INDEX
            SET @v_Sql = @v_Sql+' max(CASE rn WHEN ' + CONVERT(CHAR,@i_INDEX) + ' THEN ' + @v_tempstr +
                ' ELSE NULL END)'+@v_tempstr+'_'+CONVERT(CHAR,@i_INDEX)+','
            SET @j_INDEX=@j_INDEX+1
        END
        SET @i_INDEX=@i_INDEX+1
      END

         IF(@@error !=0)
        BEGIN
            SET @v_Error =  @@error
        END


    If (@p_Where Is Not Null AND @p_Where !='')
        BEGIN
          SET @v_Sql = Rtrim(@v_Sql) + ' from (' + @v_Partition + ' ' +
                   @p_Where + ') group by '
        END
    Else
        BEGIN
          SET @v_Sql = Rtrim(@v_Sql) + ' from (' + @v_Partition +
               ') group by '
        End

       

--    For i In 1 .. v_Keep.Count Loop
--      v_Sql := v_Sql || v_Keep(i) || ',';
--    End Loop;
      SELECT @j_INDEX =COUNT(*) FROM #KEEPTABLE
      SET @i_INDEX=1
      WHILE(@i_INDEX<=@j_INDEX)
      BEGIN
        SELECT @v_tempstr = v_Keep FROM #KEEPTABLE WHERE v_index = @i_INDEX
        SET @v_Sql = @v_Sql + LTRIM(RTRIM(@v_tempstr)) + ','
        SET @i_INDEX=@i_INDEX+1
      END


         IF(@@error !=0)
        BEGIN
            SET @v_Error =  @@error
        END

    DROP TABLE #PIVOTTABLE
    DROP TABLE #KEEPTABLE

    SET @v_Sql = Rtrim(@v_Sql)


    IF(@v_Error=0)
        exec @v_Sql
    ELSE
        Select 'x' 


  End

 

 

 

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



ALTER Procedure [dbo].[Pro_Rows_Column_Real]
         @p_Table        Varchar(1000),
         @p_Keep_Cols    Varchar(1000), --固定列，多列以“,”分开
         @p_Pivot_Col    Varchar(1000), --指定的行值转化为列名称
         @p_Pivot_Index  Varchar(1000), --指定的行值转化为列名称的排序列名称
         @p_Pivot_Val    Varchar(1000), --列值
         @p_Where        Varchar(2000)  --查询条件
--                                 p_Refc        In Out Refc
As
DECLARE @v_Sql Varchar(4000)
--    Type v_Keep_Ind_By Is Table Of Varchar2(4000) Index By Binary_Integer;
--    v_Keep v_Keep_Ind_By;
--    Type v_Pivot_Ind_By Is Table Of Varchar2(4000) Index By Binary_Integer;
--    v_Pivot       v_Pivot_Ind_By;
    DECLARE @v_Keep_Cnt    Int
    DECLARE @v_Group_By    Varchar(2000)
    DECLARE @v_Pivot_Index Varchar(100)
    DECLARE @i_INDEX  INT
    DECLARE @j_INDEX  INT
    DECLARE @k_INDEX  INT
    DECLARE @v_tempstr VARCHAR(1000)
    DECLARE @v_error  INT

    DECLARE @v_Count  INT
    DECLARE @v_INDEX  INT
    DECLARE @v_PINDEX  INT
    DECLARE @V_SSSS   VARCHAR(40)
  Begin
    --排序字段
    If @p_Pivot_Index Is Null or @p_Pivot_Index=''
        BEGIN
            SET @v_Pivot_Index = @p_Pivot_Col
        END
    Else
        BEGIN
            SET @v_Pivot_Index = @p_Pivot_Index
        END
   
    SET @v_error = 0
 
    --转化列字段名称
    SELECT @v_Keep_Cnt = Len(LTRIM(RTRIM(@p_Keep_Cols))) - Len(Replace(LTRIM(RTRIM(@p_Keep_Cols)), ',','')) + 1

--    For i In 1 .. v_Keep_Cnt Loop
--      v_Keep(i) := Fun_Split_Str(p_Keep_Cols, ',', i);
--    End Loop;

    CREATE TABLE #KEEPTABLE
   (
        v_index int,
        v_Keep VARCHAR(1000)
    )
    SET @i_INDEX=1

    WHILE(@i_INDEX<=@v_Keep_Cnt)
    BEGIN
        SET @V_SSSS=''
        SET @V_SSSS = dbo.Fun_Split_Str(@p_Keep_Cols, ',', @i_INDEX)
        INSERT INTO #KEEPTABLE VALUES(@i_INDEX,@V_SSSS)
        SET @i_INDEX=@i_INDEX+1
    END

    SET @v_Count= 0
    SELECT @v_Count=count(*) from #KEEPTABLE
    SET @v_INDEX = 1
    WHILE(@v_INDEX<=@v_Count)
    BEGIN
        SET @V_SSSS=''
        SELECT @V_SSSS = v_Keep,@v_PINDEX = v_index FROM #KEEPTABLE  WHERE v_index = @v_INDEX
        SET @v_INDEX=@v_INDEX+1
    END

     IF(@@error !=0)
        BEGIN
            SET @v_Error =  @@error
        END
   
    CREATE TABLE #TABLEPivot
    (
        p_Index    INT ,
        p_Pivot VARCHAR(200)
    )
 
    SET @v_Sql = ' INSERT INTO  #TABLEPivot select ' + ' ROW_NUMBER() OVER(ORDER BY (select 0)),CONVERT(VARCHAR(200),' + @p_Pivot_Col +
             ' ) as ' + @p_Pivot_Col + '  from ' + @p_Table +
             ' group by ' + @p_Pivot_Col + ',' + @v_Pivot_Index +
             ' Order By ' + @v_Pivot_Index
   
    Execute (@v_Sql)

    SET @v_Count= 0
    SELECT @v_Count=count(*) from #TABLEPivot
    SET @v_INDEX = 1
    WHILE(@v_INDEX<=@v_Count)
    BEGIN
        SELECT @V_SSSS = p_Pivot FROM #TABLEPivot  WHERE p_Index = @v_INDEX
        SET @v_INDEX=@v_INDEX+1
    END


     IF(@@error !=0)
        BEGIN
            SET @v_Error =  @@error
        END
   
--    Execute Immediate v_Sql Bulk Collect
--      Into v_Pivot;

   
 
    --分组字段 
--    For i In 1 .. v_Keep.Count Loop
--      v_Group_By := v_Group_By || v_Keep(i) || ',';
--    End Loop;

      SELECT @j_INDEX =COUNT(*) FROM #KEEPTABLE
      SET @i_INDEX=1
      WHILE(@i_INDEX<=@j_INDEX)
      BEGIN
        SELECT @v_tempstr = v_Keep FROM #KEEPTABLE WHERE v_index = @i_INDEX
        IF @i_INDEX = 1
            BEGIN
                SET @v_Group_By =  LTRIM(RTRIM(@v_tempstr)) + ','
            END
        ELSE
            BEGIN
                SET @v_Group_By = @v_Group_By + LTRIM(RTRIM(@v_tempstr)) + ','
            END

        SET @i_INDEX=@i_INDEX+1
      END

         IF(@@error !=0)
        BEGIN
            SET @v_Error =  @@error
        END

--    v_Group_By := Rtrim(v_Group_By, ',');
--    v_Sql      := 'select ' || v_Group_By || ',';
    SET @v_Group_By = RTRIM(@v_Group_By)
    SET @v_Group_By = SUBSTRING(@v_Group_By,0,LEN(@v_Group_By))
    SET @v_Sql = 'select '+@v_Group_By+','
 
--    For x In 1 .. v_Pivot.Count Loop
--      If v_Pivot(x) Is Not Null Then
--        v_Sql := v_Sql || ' max(decode(' || p_Pivot_Col || ',' || Chr(39) ||
--                 v_Pivot(x) || Chr(39) || ',' || p_Pivot_Val ||
--                 ',null)) as "' || v_Pivot(x) || '",';
--      End If;
--    End Loop;
      SET @j_INDEX=0
      SET @i_INDEX=1
      SELECT @j_INDEX =COUNT(*) FROM #TABLEPivot
      WHILE(@i_INDEX<=@j_INDEX)
      BEGIN
        SELECT @v_tempstr = p_Pivot FROM #TABLEPivot WHERE p_Index = @i_INDEX
        IF(@v_tempstr is NOT NULL AND LTRIM(RTRIM(@v_tempstr)) !='')
            BEGIN
                SET @v_Sql =@v_Sql+' max(CASE '+@p_Pivot_Col+ ' WHEN ' + ''''+LTRIM(RTRIM(@v_tempstr))
                +''''+' THEN '+@p_Pivot_Val +' ELSE NULL END) as '''+LTRIM(RTRIM(@v_tempstr))+''','
            END
        SET @i_INDEX=@i_INDEX+1
      END

     IF(@@error !=0)
        BEGIN
            SET @v_Error =  @@error
        END


    SET @v_Sql = Rtrim(@v_Sql)
    SET @v_Sql = SUBSTRING(@v_Sql,0,LEN(@v_Sql))

    If (@p_Where Is Not Null AND @p_Where!='')
      SET @v_Sql = @v_Sql + ' from ' + @p_Table + ' where ' + @p_Where +
               ' group by ' + @v_Group_By
    Else
      SET @v_Sql = @v_Sql + ' from ' + @p_Table + ' group by ' + @v_Group_By
   
    drop table #TABLEPivot
    drop table #KEEPTABLE

    --    Pro_Print_Sql(v_Sql);
    IF(@v_Error=0)
        EXEC (@v_Sql)
    ELSE
        Select 'x'

  End

 

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER Function [dbo].[Fun_Split_Str]
(
@p_Str Varchar(3000),
@p_Division Varchar(100),
@p_Seq Int)
    Returns Varchar(3000)
as
  Begin
declare    @v_First Int
declare    @v_Last  Int
declare    @strResult varchar(3000)

    If @p_Seq < 1
        begin
            set @strResult = ''
        end

    If @p_Seq = 1
        begin
          If dbo.StrIndexOf4(@p_Str,@p_Division,1,@p_Seq) = 0
            begin
                set @strResult = @p_Str
            end
          Else
            begin
                set @strResult =Substring(@p_Str, 1, CHARINDEX(@p_Division,@p_Str, 1) - 1)
            end
        End
    Else
        begin
          set @v_First = dbo.StrIndexOf4(@p_Str,@p_Division,1,@p_Seq-1)
          set @v_Last  = dbo.StrIndexOf4(@p_Str,@p_Division,1,@p_Seq)
          If (@v_Last = 0)
            begin
                If (@v_First > 0)
                    begin
                      set @strResult = Substring(@p_Str, @v_First + 1,len(@p_Str))
                    end 
                Else
                    begin
                      set @strResult = ''
                    end
            End
          Else
            begin
                set @strResult = Substring(@p_Str, @v_First + 1, @v_Last - @v_First - 1)
            end
        End
    RETURN @strResult
    End




Fun_Split_Str使用到了外部DLL

在Visual Studio中新建一个类，创建下面方法，部署到MSSQL2005数据库就可以了

方法：

[Microsoft.SqlServer.Server.SqlFunction]
    public static SqlInt32 StrIndexOf4(SqlChars str, SqlChars strchar, SqlInt32 StartPosition,SqlInt32 Seq)
    {
        //有问题
        //string A = new string(strA.Value);
        //string B = new string(Value.Value);
        //int i = A.IndexOf(B,startIndex.Value,Count.Value);
        //return i;

        string A = new string(str.Value);
        string B = new string(strchar.Value);

        if (Seq.Value<=0)
        {
            return A.IndexOf(B, StartPosition.Value) + 1;
        }
        else
        {
            string C = A.Substring(StartPosition.Value, A.Length - StartPosition.Value);
            int n = 0;

            for (int j = 0; j < C.Length;j++ )
            {
                if (C.IndexOf(B) == 0)
                {
                    n += 1;
                    if (n == Seq.Value)
                    {
                        return A.Length - C.Length+1;
                    }
                }

                C = C.Substring(1, C.Length - 1);
                j = 0;
            }

            return 0;
        }

    }

 

 

 

提醒MSSQL需要打开 clr Enabled

执行脚本：

 

exec sp_configure 'show advanced options', '1';
go
reconfigure;
go
exec sp_configure 'clr enabled', '1'
go
reconfigure;
exec sp_configure 'show advanced options', '1';
go