以前总是在网上搜一些交叉表生成的相关代码 但是使用起来总是很复杂 看看 刚出炉的东西对你来说是不是有所帮助 ：_）
先看看下一个生成的存储过程 ：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
