用于生成交叉表的存储过程的存储过程 ：_）使用起来超简单

以前总是在网上搜一些交叉表生成的相关代码 但是使用起来总是很复杂 看看 刚出炉的东西对你来说是不是有所帮助 ：_）

先看看下一个生成的存储过程 ：SQLServer2000

Create Procedure [dbo].[pGet_Cross_Simple_Report_For_统计生成表_By_工龄编码_职称编号]
As
--pGet_Cross_By_统计生成表
Begin
--CREATE TABLE [统计生成表](
-- [工龄编码] int NOT NULL,
-- [职称编号] [int] NOT NULL,
-- [统计值] [int] NULL,
-- CONSTRAINT [PK_统计生成表] PRIMARY KEY CLUSTERED
--(
-- [工龄编码] ASC,
-- [职称编号] ASC
--) ON [PRIMARY]
--) ON [PRIMARY]

Declare @SQL        nvarchar(4000)
Declare @SQL_Create nvarchar(2000)
Declare @SQL_Insert nvarchar(2000)
Declare @SQL_InsertValues nvarchar(2000)
Declare @SQL_InsertValuesDeclare nvarchar(2000)
Declare @SQL_Select nvarchar(2000)
Declare @SQL_SetDefaultValues nvarchar(2000)
Select @SQL_Create = 'Declare @AcrossTable Table(a nvarchar(100)'
Select @SQL_Insert = '
Insert into @AcrossTable (a'
Select @SQL_InsertValues  = 'Values ( @a'
Select @SQL_InsertValuesDeclare = 'Declare @a nvarchar(100)
'
Select @SQL_Select = 'Select a'
Select @SQL_SetDefaultValues = 'Select @a = ''''
'
Declare @ColCode int

DECLARE The_I CURSOR FOR Select Distinct 职称编号 From  统计生成表  Order By 职称编号 ASC
OPEN The_I
FETCH NEXT FROM The_I  INTO @ColCode
WHILE @@FETCH_STATUS = 0
BEGIN
 Select @SQL_Create = @SQL_Create + ',b' + Convert(varchar(10),@ColCode)+ ' nvarchar(100)'
 Select @SQL_Insert = @SQL_Insert + ',b' + Convert(varchar(10),@ColCode)
 Select @SQL_InsertValues = @SQL_InsertValues + ',@b' + Convert(varchar(10),@ColCode)
 Select @SQL_InsertValuesDeclare = @SQL_InsertValuesDeclare + 'Declare @b' + Convert(varchar(10),@ColCode) + ' nvarchar(100)
 '
 Select @SQL_Select = @SQL_Select + ',b' + Convert(varchar(10),@ColCode)
 Select @SQL_SetDefaultValues = @SQL_SetDefaultValues + 'Select @b' + Convert(varchar(10),@ColCode) + ' = ''''
 '
 FETCH NEXT FROM The_I  INTO @ColCode
End
CLOSE The_I
DEALLOCATE The_I

--得到动态交叉表的创建语句
--例如：Declare @AcrossTable Table(a nvarchar(100),b1 nvarchar(100),b2 nvarchar(100),b3 nvarchar(100),b4 nvarchar(100),b5 nvarchar(100) )
Select @SQL_Create = @SQL_Create + ' )'

--得到动态交叉表的Insert语句前半部分
--例如: Insert into @AcrossTable (a,b1,b2,b3,b4,b5 )
Select @SQL_Insert = @SQL_Insert + ' )'

--得到动态交叉表的Insert语句后半部分
--例如： Values ( @a,@b1,@b2,@b3,@b4,@b5 )
Select @SQL_InsertValues = @SQL_InsertValues + ' )'

--得到动态交叉表的Select语句
--例如：Select a,b1,b2,b3,b4,b5,b6 From @AcrossTable
Select @SQL_Select = @SQL_Select + ' From @AcrossTable'

Declare @SQL_InsertX nvarchar(2000)
Select  @SQL_InsertX = ''
Declare @SQL_SelectX nvarchar(2000)
Declare @RowCode int
Declare @Value   int
Declare @RowCode_Count int
Declare @Count int
Select  @Count = (Select count(*)  From (Select Distinct 工龄编码 From 统计生成表) a)
Declare The_X Cursor For Select  工龄编码,Count(*) From    统计生成表 Group By 工龄编码 Order By 工龄编码 Asc
OPEN The_X
FETCH NEXT FROM The_X  INTO @RowCode,@RowCode_Count
WHILE  @Count > 0
BEGIN
  Select @SQL_SelectX = 'Select @a =  ' + Convert(varchar(10),@RowCode) + '
 '
  DECLARE The_Y CURSOR FOR
  Select 职称编号,统计值  From  统计生成表 Where 工龄编码 = @RowCode  Order By 职称编号 ASC
  OPEN The_Y
  FETCH NEXT FROM The_Y  INTO @ColCode,@Value
  WHILE @@FETCH_STATUS = 0
  BEGIN
   Select @SQL_SelectX = @SQL_SelectX + 'Select @b' + Convert(varchar(10),@ColCode) + ' = ' + Convert(varchar(10),@Value) + '
 '
   FETCH NEXT FROM The_Y  INTO @ColCode,@Value
  End
  CLOSE The_Y
  DEALLOCATE The_Y
  Select @Count = @Count - 1
  Select @SQL_InsertX = @SQL_InsertX  + '
 '+ @SQL_SelectX + '
 '+ @SQL_Insert + '
 '+ @SQL_InsertValues + '
 '+ @SQL_SetDefaultValues + '
 '
 FETCH NEXT FROM The_X  INTO @RowCode,@RowCode_Count
End
CLOSE The_X
DEALLOCATE The_X

Select @SQL = @SQL_InsertValuesDeclare + '
' + @SQL_Create + '
' + @SQL_InsertX + '
' + @SQL_Select

Print @SQL

Execute (@SQL )

end

结果 ：

1   1   2  3   4   5
2   6   7  8   9  10
3 11 12 13 14 15
4 16 17 18 19 20



再给出一个复杂的生成后的存储过程

Create Procedure [dbo].[pGet_Cross_Report_For_统计生成表_By_工龄编码_职称编号]
As
Begin
--CREATE TABLE [工龄工资标准表](
-- [工龄编码] [int] IDENTITY(1,1) NOT NULL,
-- [工龄名称] [nvarchar](40)  NULL

--CREATE TABLE [职称信息表](
-- [职称编号] [int] IDENTITY(1,1) NOT NULL,
-- [职称名称] [nvarchar](40)  NULL

--CREATE TABLE [TableName_By_Stat%>](
-- [工龄编码] [int] NOT NULL,
-- [职称编号] [int] NOT NULL,
-- [统计值] [int] NULL,

Declare @SQL        nvarchar(4000)
Declare @SQL_Create nvarchar(2000)
Declare @SQL_Insert nvarchar(2000)
Declare @SQL_InsertValues nvarchar(2000)
Declare @SQL_InsertValuesDeclare nvarchar(2000)
Declare @SQL_Select nvarchar(2000)
Declare @SQL_Title nvarchar(2000)
Declare @SQL_TitleValues nvarchar(2000)
Declare @SQL_SetDefaultValues nvarchar(2000)
Select @SQL_Create = 'Declare @AcrossTable Table(a nvarchar(100)'
Select @SQL_Insert = '
Insert into @AcrossTable (a'
Select @SQL_InsertValues  = 'Values ( @a'
Select @SQL_InsertValuesDeclare = 'Declare @a nvarchar(100)
'
Select @SQL_Select = 'Select a'
Select @SQL_TitleValues = '
Select @a = '''''
Select @SQL_SetDefaultValues = 'Select @a = ''''
'
Declare @ColCode int
Declare @ColName nvarchar(100)

DECLARE The_I CURSOR FOR Select 职称编号,职称名称 From  职称信息表  Order By 职称编号 ASC
OPEN The_I
FETCH NEXT FROM The_I  INTO @ColCode,@ColName
WHILE @@FETCH_STATUS = 0
BEGIN
 Select @SQL_TitleValues = @SQL_TitleValues + '
 Select @b' + Convert(varchar(10),@ColCode) + ' = ''' + @ColName +''''
 Select @SQL_Create = @SQL_Create + ',b' + Convert(varchar(10),@ColCode)+ ' nvarchar(100)'
 Select @SQL_Insert = @SQL_Insert + ',b' + Convert(varchar(10),@ColCode)
 Select @SQL_InsertValues = @SQL_InsertValues + ',@b' + Convert(varchar(10),@ColCode)
 Select @SQL_InsertValuesDeclare = @SQL_InsertValuesDeclare + 'Declare @b' + Convert(varchar(10),@ColCode) + ' nvarchar(100)
 '
 Select @SQL_Select = @SQL_Select + ',b' + Convert(varchar(10),@ColCode)
 Select @SQL_SetDefaultValues = @SQL_SetDefaultValues + 'Select @b' + Convert(varchar(10),@ColCode) + ' = ''''
 '
 FETCH NEXT FROM The_I  INTO @ColCode,@ColName
End
CLOSE The_I
DEALLOCATE The_I

--得到动态交叉表的创建语句
--例如：Declare @AcrossTable Table(a nvarchar(100),b1 nvarchar(100),b2 nvarchar(100),b3 nvarchar(100),b4 nvarchar(100),b5 nvarchar(100) )
Select @SQL_Create = @SQL_Create + ' )'

--得到动态交叉表的Insert语句前半部分
--例如: Insert into @AcrossTable (a,b1,b2,b3,b4,b5 )
Select @SQL_Insert = @SQL_Insert + ' )'

--得到动态交叉表的Insert语句后半部分
--例如： Values ( @a,@b1,@b2,@b3,@b4,@b5 )
Select @SQL_InsertValues = @SQL_InsertValues + ' )'

--得到动态交叉表的Select语句
--例如：Select a,b1,b2,b3,b4,b5,b6 From @AcrossTable
Select @SQL_Select = @SQL_Select + ' From @AcrossTable'

--得到动态交叉表的Title语句
--例如：Select @a = ''
--  Select @b1 = '员级及以下'
--  Select @b2 = '初级职称'
--  Select @b3 = '中级职称'
--  Select @b4 = '高级职称'
--  Select @b5 = '正高级(相当于正高级)职称'
--  Insert into @AcrossTable (a,b1,b2,b3,b4,b5 )
--  Values ( @a,@b1,@b2,@b3,@b4,@b5 )
Select @SQL_Title = @SQL_TitleValues + '
' + @SQL_Insert + '
' + @SQL_InsertValues

Declare @SQL_InsertX nvarchar(2000)
Select  @SQL_InsertX = ''
Declare @SQL_SelectX nvarchar(2000)
Declare @RowCode int
Declare @Value   int
Declare @RowCode_Count int
Declare @Count int
Select  @Count = Count(*) From 工龄工资标准表
Declare The_X Cursor For Select  工龄编码,Count(*) From    统计生成表 Group By 工龄编码 Order By 工龄编码 Asc
OPEN The_X
FETCH NEXT FROM The_X  INTO @RowCode,@RowCode_Count
WHILE  @Count > 0
BEGIN
  Select @SQL_SelectX = 'Select @a = 工龄名称 From 工龄工资标准表 Where 工龄编码 = ' + Convert(varchar(10),@RowCode) + '
 '
  DECLARE The_Y CURSOR FOR
  Select 职称编号,统计值  From  统计生成表 Where 工龄编码 = @RowCode  Order By 职称编号 ASC
  OPEN The_Y
  FETCH NEXT FROM The_Y  INTO @ColCode,@Value
  WHILE @@FETCH_STATUS = 0
  BEGIN
   Select @SQL_SelectX = @SQL_SelectX + 'Select @b' + Convert(varchar(10),@ColCode) + ' = ' + Convert(varchar(10),@Value) + '
 '
   FETCH NEXT FROM The_Y  INTO @ColCode,@Value
  End
  CLOSE The_Y
  DEALLOCATE The_Y
  Select @Count = @Count - 1
  Select @SQL_InsertX = @SQL_InsertX  + '
 '+ @SQL_SelectX + '
 '+ @SQL_Insert + '
 '+ @SQL_InsertValues + '
 '+ @SQL_SetDefaultValues + '
 '
 FETCH NEXT FROM The_X  INTO @RowCode,@RowCode_Count
End
CLOSE The_X
DEALLOCATE The_X

Select @SQL = @SQL_InsertValuesDeclare + '
' + @SQL_Create + '
' + @SQL_Title + '
' + @SQL_SetDefaultValues + '
' + @SQL_InsertX + '
' + @SQL_Select

Print @SQL

Execute (@SQL )

end

结果：
 员级及以下 初级职称 中级职称 高级职称 正高级(相当于正高级)职称 XX
1-10年工龄                 1              2               3                                             4     5 
11-20年工龄               6              7               8                                             9    10 
21-30年工龄             11            12             13                                           14    15 
大于30年工龄          16            17             18                                            19    20 

---------------------
：_）
现在给出生成这两个存储过程的存储过程

Create Procedure [dbo].[pCreateProcedure_CrossSimpleReport] 
@TableName varchar(100) ,
@RowName varchar(500) ,
@ColumnName varchar(500) ,
@Value  varchar(500)
AS
Begin

--Declare @TableName varchar(100)
--Declare @RowName varchar(500)
--Declare @ColumnName varchar(500)
--Declare @Value  varchar(500)
--
--Select @TableName = '统计生成表'
--Select @RowName = '工龄编码'
--Select @ColumnName = '职称编号'
--Select @Value   = '统计值'
--
--Execute pCreateProcedure_CrossSimpleReport @TableName,@RowName,@ColumnName,@Value
--execute pGet_Cross_Simple_Report_For_统计生成表_By_工龄编码_职称编号
Declare @CreateSQL varchar(8000)

Select @CreateSQL = '

Create Procedure pGet_Cross_Simple_Report_For_<%TableName%>_By_<%RowName%>_<%ColumnName%>
As
--pGet_Cross_By_<%TableName%>
Begin
--CREATE TABLE [<%TableName%>](
-- [<%RowName%>] [int] NOT NULL,
-- [<%ColumnName%>] [int] NOT NULL,
-- [<%Value%>] [int] NULL,
-- CONSTRAINT [PK_<%TableName%>] PRIMARY KEY CLUSTERED
--(
-- [<%RowName%>] ASC,
-- [<%ColumnName%>] ASC
--) ON [PRIMARY]
--) ON [PRIMARY]

Declare @SQL        nvarchar(4000)
Declare @SQL_Create nvarchar(2000)
Declare @SQL_Insert nvarchar(2000)
Declare @SQL_InsertValues nvarchar(2000)
Declare @SQL_InsertValuesDeclare nvarchar(2000)
Declare @SQL_Select nvarchar(2000)
Declare @SQL_SetDefaultValues nvarchar(2000)
Select @SQL_Create = ''Declare @AcrossTable Table(a nvarchar(100)''
Select @SQL_Insert = ''
Insert into @AcrossTable (a''
Select @SQL_InsertValues  = ''Values ( @a''
Select @SQL_InsertValuesDeclare = ''Declare @a nvarchar(100)
''
Select @SQL_Select = ''Select a''
Select @SQL_SetDefaultValues = ''Select @a = ''''''''
''
Declare @ColCode int

DECLARE The_I CURSOR FOR Select Distinct <%ColumnName%> From  <%TableName%>  Order By <%ColumnName%> ASC
OPEN The_I
FETCH NEXT FROM The_I  INTO @ColCode
WHILE @@FETCH_STATUS = 0
BEGIN
 Select @SQL_Create = @SQL_Create + '',b'' + Convert(varchar(10),@ColCode)+ '' nvarchar(100)''
 Select @SQL_Insert = @SQL_Insert + '',b'' + Convert(varchar(10),@ColCode)
 Select @SQL_InsertValues = @SQL_InsertValues + '',@b'' + Convert(varchar(10),@ColCode)
 Select @SQL_InsertValuesDeclare = @SQL_InsertValuesDeclare + ''Declare @b'' + Convert(varchar(10),@ColCode) + '' nvarchar(100)
 ''
 Select @SQL_Select = @SQL_Select + '',b'' + Convert(varchar(10),@ColCode)
 Select @SQL_SetDefaultValues = @SQL_SetDefaultValues + ''Select @b'' + Convert(varchar(10),@ColCode) + '' = ''''''''
 ''
 FETCH NEXT FROM The_I  INTO @ColCode
End
CLOSE The_I
DEALLOCATE The_I

--得到动态交叉表的创建语句
--例如：Declare @AcrossTable Table(a nvarchar(100),b1 nvarchar(100),b2 nvarchar(100),b3 nvarchar(100),b4 nvarchar(100),b5 nvarchar(100) )
Select @SQL_Create = @SQL_Create + '' )''

--得到动态交叉表的Insert语句前半部分
--例如: Insert into @AcrossTable (a,b1,b2,b3,b4,b5 )
Select @SQL_Insert = @SQL_Insert + '' )''

--得到动态交叉表的Insert语句后半部分
--例如： Values ( @a,@b1,@b2,@b3,@b4,@b5 )
Select @SQL_InsertValues = @SQL_InsertValues + '' )''

--得到动态交叉表的Select语句
--例如：Select a,b1,b2,b3,b4,b5,b6 From @AcrossTable
Select @SQL_Select = @SQL_Select + '' From @AcrossTable''

Declare @SQL_InsertX nvarchar(2000)
Select  @SQL_InsertX = ''''
Declare @SQL_SelectX nvarchar(2000)
Declare @RowCode int
Declare @Value   int
Declare @RowCode_Count int
Declare @Count int
Select  @Count = (Select count(*)  From (Select Distinct <%RowName%> From <%TableName%>) a)
Declare The_X Cursor For Select  <%RowName%>,Count(*) From    <%TableName%> Group By <%RowName%> Order By <%RowName%> Asc
OPEN The_X
FETCH NEXT FROM The_X  INTO @RowCode,@RowCode_Count
WHILE  @Count > 0
BEGIN
  Select @SQL_SelectX = ''Select @a =  '' + Convert(varchar(10),@RowCode) + ''
 ''
  DECLARE The_Y CURSOR FOR
  Select <%ColumnName%>,<%Value%>  From  <%TableName%> Where <%RowName%> = @RowCode  Order By <%ColumnName%> ASC
  OPEN The_Y
  FETCH NEXT FROM The_Y  INTO @ColCode,@Value
  WHILE @@FETCH_STATUS = 0
  BEGIN
   Select @SQL_SelectX = @SQL_SelectX + ''Select @b'' + Convert(varchar(10),@ColCode) + '' = '' + Convert(varchar(10),@Value) + ''
 ''
   FETCH NEXT FROM The_Y  INTO @ColCode,@Value
  End
  CLOSE The_Y
  DEALLOCATE The_Y
  Select @Count = @Count - 1
  Select @SQL_InsertX = @SQL_InsertX  + ''
 ''+ @SQL_SelectX + ''
 ''+ @SQL_Insert + ''
 ''+ @SQL_InsertValues + ''
 ''+ @SQL_SetDefaultValues + ''
 ''
 FETCH NEXT FROM The_X  INTO @RowCode,@RowCode_Count
End
CLOSE The_X
DEALLOCATE The_X

Select @SQL = @SQL_InsertValuesDeclare + ''
'' + @SQL_Create + ''
'' + @SQL_InsertX + ''
'' + @SQL_Select

Print @SQL

Execute (@SQL )

end

'

Select @CreateSQL = Replace(@CreateSQL,'<%TableName%>',@TableName) 
Select @CreateSQL = Replace(@CreateSQL,'<%RowName%>',@RowName) 
Select @CreateSQL = Replace(@CreateSQL,'<%ColumnName%>',@ColumnName) 
Select @CreateSQL = Replace(@CreateSQL,'<%Value%>',@Value)  

Print @CreateSQL

Execute(@CreateSQL)

End

+=======================================

Create Procedure [dbo].[pCreateProcedure_CrossReport] 
@TableName_By_Stat varchar(100) ,
@Stat_Value  varchar(500) ,

@TableName_By_Row varchar(100),
@Stat_RowCode varchar(500) ,
@Row_RowName varchar(500),

@TableName_By_Column varchar(100),
@Stat_ColumnCode varchar(500) ,
@Column_RowName varchar(500)
AS
Begin

--Declare @TableName_By_Stat varchar(100)
--Declare @Stat_Value  varchar(500)
--Declare @TableName_By_Row varchar(100)
--Declare @Stat_RowCode varchar(500)
--Declare @Row_RowName varchar(500)
--Declare @TableName_By_Column varchar(100)
--Declare @Stat_ColumnCode varchar(500)
--Declare @Column_RowName varchar(500)
--
--Select @TableName_By_Stat ='统计生成表'
--Select @Stat_Value  ='统计值'
--Select @TableName_By_Row ='工龄工资标准表'
--Select @Stat_RowCode ='工龄编码'
--Select @Row_RowName ='工龄名称'
--Select @TableName_By_Column ='职称信息表'
--Select @Stat_ColumnCode ='职称编号'
--Select @Column_RowName ='职称名称'
--
--Execute [pCreateProcedure_CrossReport] @TableName_By_Stat ,@Stat_Value ,@TableName_By_Row ,@Stat_RowCode ,@Row_RowName ,@TableName_By_Column ,@Stat_ColumnCode ,@Column_RowName
--execute pGet_Cross_Report_For_统计生成表_By_工龄编码_职称编号
Declare @CreateSQL varchar(8000)

Select @CreateSQL ='
Create Procedure pGet_Cross_Report_For_<%TableName_By_Stat%>_By_<%Stat_RowCode%>_<%Stat_ColumnCode%>
As
Begin
--CREATE TABLE [<%TableName_By_Row%>](
-- [<%Stat_RowCode%>] [int] IDENTITY(1,1) NOT NULL,
-- [<%Row_RowName%>] [nvarchar](40)  NULL

--CREATE TABLE [<%TableName_By_Column%>](
-- [<%Stat_ColumnCode%>] [int] IDENTITY(1,1) NOT NULL,
-- [<%Column_RowName%>] [nvarchar](40)  NULL

--CREATE TABLE [TableName_By_Stat%>](
-- [<%Stat_RowCode%>] [int] NOT NULL,
-- [<%Stat_ColumnCode%>] [int] NOT NULL,
-- [<%Stat_Value%>] [int] NULL,

Declare @SQL        nvarchar(4000)
Declare @SQL_Create nvarchar(2000)
Declare @SQL_Insert nvarchar(2000)
Declare @SQL_InsertValues nvarchar(2000)
Declare @SQL_InsertValuesDeclare nvarchar(2000)
Declare @SQL_Select nvarchar(2000)
Declare @SQL_Title nvarchar(2000)
Declare @SQL_TitleValues nvarchar(2000)
Declare @SQL_SetDefaultValues nvarchar(2000)
Select @SQL_Create = ''Declare @AcrossTable Table(a nvarchar(100)''
Select @SQL_Insert = ''
Insert into @AcrossTable (a''
Select @SQL_InsertValues  = ''Values ( @a''
Select @SQL_InsertValuesDeclare = ''Declare @a nvarchar(100)
''
Select @SQL_Select = ''Select a''
Select @SQL_TitleValues = ''
Select @a = ''''''''''
Select @SQL_SetDefaultValues = ''Select @a = ''''''''
''
Declare @ColCode int
Declare @ColName nvarchar(100)

DECLARE The_I CURSOR FOR Select <%Stat_ColumnCode%>,<%Column_RowName%> From  <%TableName_By_Column%>  Order By <%Stat_ColumnCode%> ASC
OPEN The_I
FETCH NEXT FROM The_I  INTO @ColCode,@ColName
WHILE @@FETCH_STATUS = 0
BEGIN
 Select @SQL_TitleValues = @SQL_TitleValues + ''
 Select @b'' + Convert(varchar(10),@ColCode) + '' = '''''' + @ColName +''''''''
 Select @SQL_Create = @SQL_Create + '',b'' + Convert(varchar(10),@ColCode)+ '' nvarchar(100)''
 Select @SQL_Insert = @SQL_Insert + '',b'' + Convert(varchar(10),@ColCode)
 Select @SQL_InsertValues = @SQL_InsertValues + '',@b'' + Convert(varchar(10),@ColCode)
 Select @SQL_InsertValuesDeclare = @SQL_InsertValuesDeclare + ''Declare @b'' + Convert(varchar(10),@ColCode) + '' nvarchar(100)
 ''
 Select @SQL_Select = @SQL_Select + '',b'' + Convert(varchar(10),@ColCode)
 Select @SQL_SetDefaultValues = @SQL_SetDefaultValues + ''Select @b'' + Convert(varchar(10),@ColCode) + '' = ''''''''
 ''
 FETCH NEXT FROM The_I  INTO @ColCode,@ColName
End
CLOSE The_I
DEALLOCATE The_I

--得到动态交叉表的创建语句
--例如：Declare @AcrossTable Table(a nvarchar(100),b1 nvarchar(100),b2 nvarchar(100),b3 nvarchar(100),b4 nvarchar(100),b5 nvarchar(100) )
Select @SQL_Create = @SQL_Create + '' )''

--得到动态交叉表的Insert语句前半部分
--例如: Insert into @AcrossTable (a,b1,b2,b3,b4,b5 )
Select @SQL_Insert = @SQL_Insert + '' )''

--得到动态交叉表的Insert语句后半部分
--例如： Values ( @a,@b1,@b2,@b3,@b4,@b5 )
Select @SQL_InsertValues = @SQL_InsertValues + '' )''

--得到动态交叉表的Select语句
--例如：Select a,b1,b2,b3,b4,b5,b6 From @AcrossTable
Select @SQL_Select = @SQL_Select + '' From @AcrossTable''

--得到动态交叉表的Title语句
--例如：Select @a = ''''
--  Select @b1 = ''员级及以下''
--  Select @b2 = ''初级职称''
--  Select @b3 = ''中级职称''
--  Select @b4 = ''高级职称''
--  Select @b5 = ''正高级(相当于正高级)职称''
--  Insert into @AcrossTable (a,b1,b2,b3,b4,b5 )
--  Values ( @a,@b1,@b2,@b3,@b4,@b5 )
Select @SQL_Title = @SQL_TitleValues + ''
'' + @SQL_Insert + ''
'' + @SQL_InsertValues

Declare @SQL_InsertX nvarchar(2000)
Select  @SQL_InsertX = ''''
Declare @SQL_SelectX nvarchar(2000)
Declare @RowCode int
Declare @Value   int
Declare @RowCode_Count int
Declare @Count int
Select  @Count = Count(*) From <%TableName_By_Row%>
Declare The_X Cursor For Select  <%Stat_RowCode%>,Count(*) From    <%TableName_By_Stat%> Group By <%Stat_RowCode%> Order By <%Stat_RowCode%> Asc
OPEN The_X
FETCH NEXT FROM The_X  INTO @RowCode,@RowCode_Count
WHILE  @Count > 0
BEGIN
  Select @SQL_SelectX = ''Select @a = <%Row_RowName%> From <%TableName_By_Row%> Where <%Stat_RowCode%> = '' + Convert(varchar(10),@RowCode) + ''
 ''
  DECLARE The_Y CURSOR FOR
  Select <%Stat_ColumnCode%>,<%Stat_Value%>  From  <%TableName_By_Stat%> Where <%Stat_RowCode%> = @RowCode  Order By <%Stat_ColumnCode%> ASC
  OPEN The_Y
  FETCH NEXT FROM The_Y  INTO @ColCode,@Value
  WHILE @@FETCH_STATUS = 0
  BEGIN
   Select @SQL_SelectX = @SQL_SelectX + ''Select @b'' + Convert(varchar(10),@ColCode) + '' = '' + Convert(varchar(10),@Value) + ''
 ''
   FETCH NEXT FROM The_Y  INTO @ColCode,@Value
  End
  CLOSE The_Y
  DEALLOCATE The_Y
  Select @Count = @Count - 1
  Select @SQL_InsertX = @SQL_InsertX  + ''
 ''+ @SQL_SelectX + ''
 ''+ @SQL_Insert + ''
 ''+ @SQL_InsertValues + ''
 ''+ @SQL_SetDefaultValues + ''
 ''
 FETCH NEXT FROM The_X  INTO @RowCode,@RowCode_Count
End
CLOSE The_X
DEALLOCATE The_X

Select @SQL = @SQL_InsertValuesDeclare + ''
'' + @SQL_Create + ''
'' + @SQL_Title + ''
'' + @SQL_SetDefaultValues + ''
'' + @SQL_InsertX + ''
'' + @SQL_Select

Print @SQL

Execute (@SQL )

end

'
Select @CreateSQL = Replace(@CreateSQL,'<%TableName_By_Stat%>',@TableName_By_Stat) 
Select @CreateSQL = Replace(@CreateSQL,'<%Stat_Value%>',@Stat_Value) 

Select @CreateSQL = Replace(@CreateSQL,'<%TableName_By_Row%>',@TableName_By_Row) 
Select @CreateSQL = Replace(@CreateSQL,'<%Stat_RowCode%>',@Stat_RowCode) 
Select @CreateSQL = Replace(@CreateSQL,'<%Row_RowName%>',@Row_RowName) 

Select @CreateSQL = Replace(@CreateSQL,'<%TableName_By_Column%>',@TableName_By_Column)  
Select @CreateSQL = Replace(@CreateSQL,'<%Stat_ColumnCode%>',@Stat_ColumnCode) 
Select @CreateSQL = Replace(@CreateSQL,'<%Column_RowName%>',@Column_RowName) 

 

Print @CreateSQL

Execute(@CreateSQL)

End