利用存储过程实现交叉表格式数据查询的一种通用方法

问题描述：如何生成以下表结构的交叉报表格式的数据查询。  

CREATE TABLE [dbo].[testTable] (

       [id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,

       [dDate] [datetime] NOT NULL ,

       [dTime] [int] NOT NULL ,

       [value] [decimal](18, 4) NULL 

) ON [PRIMARY]

GO 

ALTER TABLE [dbo].[testTable] ADD

       CONSTRAINT [PK_testTable] PRIMARY KEY  CLUSTERED

       (

              [id],

              [dDate],

              [dTime]

       )  ON [PRIMARY]

GO 

数据如下表所示：

Id	dDate	dTime	value
压力1	<?xml:namespace prefix = st1 />2005-1-1	8	15.23
压力1	2005-1-1	9	14.26
压力1	2005-1-1	10	18.78
压力1	2005-1-1	11	20.12
压力1	2005-1-1	12	30.25
压力2	2005-1-1	8	18.23
压力2	2005-1-1	9	19.26
压力2	2005-1-1	10	14.78
压力2	2005-1-1	11	22.12
压力2	2005-1-1	12	20.25
压力3	2005-1-1	8	16.23
压力3	2005-1-1	9	29.26
压力3	2005-1-1	10	24.78
压力3	2005-1-1	11	25.12
压力3	2005-1-1	12	24.25

表一 

生成的报表格式如下： 

日期	时间	压力1	压力2	压力3
2005-1-1	8	15.23	18.23	16.23
2005-1-1	9	14.26	19.26	29.26
2005-1-1	10	18.78	14.78	24.78
2005-1-1	11	20.12	22.12	25.12
2005-1-1	12	30.25	20.25	24.25

表二 

表格经过转换（转换为交叉表格式），压缩了重复的数据，而且也便于查看和比较数据间的关系。 

实现原理：

1．生成时间临时表，格式如下表： 

日期	时间
2005-1-1	8
2005-1-1	9
2005-1-1	10
2005-1-1	11
2005-1-1	12

表三 

2．再生成以下临时表，格式如下表： 

日期	时间	压力1
2005-1-1	8	15.23
2005-1-1	9	14.26
2005-1-1	10	18.78
2005-1-1	11	20.12
2005-1-1	12	30.25

表四 

日期	时间	压力2
2005-1-1	8	18.23
2005-1-1	9	19.26
2005-1-1	10	14.78
2005-1-1	11	22.12
2005-1-1	12	20.25

表五 

日期	时间	压力3
2005-1-1	8	16.23
2005-1-1	9	29.26
2005-1-1	10	24.78
2005-1-1	11	25.12
2005-1-1	12	24.25

表六 

3．然后使用SQL语句把它们连接起来，形成表二格式。

4．为了使得存储过程具有极大的灵活性，能够适应不同的表名称、不同的列名称、查询不同的列和查询不同的时间，应当使用动态SQL语句编写存储过程。

 

存储过程语句如下： 

CREATE     PROCEDURE pSearchData
    @tableName varchar( 100 ),@columnFieldName varchar( 100 ),@dateFieldName varchar( 100 ),@timeFieldName varchar( 100 ),@valueFieldName varchar( 100 ),
    @searchColumn varchar( 1000 ),@SearchTime varchar( 1000 )
-- @tableName 为查询表名，@columnFieldName 为查询列字段名，@dateFieldName 为查询日期字段名，@timeFieldName 为查询时间字段名，@valueFieldName 为查询值字段名
-- @searchColumn 为要查询的列的字符串，@SearchTime 为要查询的时间字符串
AS
declare @i  int ,@j  int ,@dTime  int ,@timeRowNum  int ,@colID  int
declare @dDate datetime
declare @sql varchar( 4000 ),@sqlTableName varchar( 1000 ),@sqlJoinSelect varchar( 4000 ),@sqlJoinFrom varchar( 4000 )

set  @i  =  dbo.fGetParamTotal(@searchColumn)
set  @timeRowNum  =  dbo.fGetParamTotal(@SearchTime)
set  @sql  =   ''
set  @sqlJoinSelect  =   ' select Convert(varchar(2),#tempTime.dTime) + '' :00 '' as 时间 '
set  @sqlJoinFrom  =   '  from #tempTime  '
set  @colID  =   1

--  建立临时时间表，并插入数据
CREATE TABLE [dbo].[#tempTime] ([dDate] [datetime] NOT NULL ,[dTime] [ int ] NOT NULL ) ON [PRIMARY]
while  (@timeRowNum  > 0 )
begin 

     set  @dDate  =  Convert(datetime,dbo.fGetParamValue(@SearchTime,@timeRowNum))
     set  @dDate  =  Convert(datetime,dbo.fToDate(@dDate))
     set  @dTime  =  datepart(hour,Convert(datetime,dbo.fGetParamValue(@SearchTime,@timeRowNum)))
    
    INSERT INTO #tempTime(dDate, dTime) VALUES (@dDate, @dTime)
     set  @timeRowNum  =  @timeRowNum  -   1
end
-- 建立动态sql语句生成临时表,并且连接临时表
if  @i  <>   0
begin
     while  (@i  >   0 )
    begin 
         set  @sqlTableName  =   ' #tempValue ' +   Convert(varchar( 2 ),@colID)
         set  @sql  =  @sql  +  N ' select  ' +  @columnFieldName  +  N ' , ' + @dateFieldName  + N ' , ' + @timeFieldName + N ' , ' + @valueFieldName  + N '  into  '   +  @sqlTableName  +  N '  
                 from  ' +@tableName+N '  where  ' + @columnFieldName + N '   =   ''' + Convert(varchar(1000),dbo.fGetParamValue(@searchColumn,@colID)) +N ''''

         -- 求第一个查询日期和时间
         set  @dDate  =  Convert(datetime,dbo.fGetParamValue(@SearchTime, 1 ))
         set  @dDate  =  Convert(datetime,dbo.fToDate(@dDate))
         set  @dTime  =  datepart(hour,Convert(datetime,dbo.fGetParamValue(@SearchTime, 1 )))

         set  @sql  =  @sql  +  N '  and ((( '   +  @dateFieldName  +  N '  =  '''   +  Convert(varchar( 100 ),@dDate, 121 )  +  N ''' ) and ( ' +  @timeFieldName  +  N '  =  ' +  Convert(varchar( 100 ),@dTime)  +  N ' )) '
         set  @j  =   2
         while  (@j  <=  dbo.fGetParamTotal(@SearchTime))
        begin
             set  @dDate  =  Convert(datetime,dbo.fGetParamValue(@SearchTime,@j))
             set  @dDate  =  Convert(datetime,dbo.fToDate(@dDate))
             set  @dTime  =  datepart(hour,Convert(datetime,dbo.fGetParamValue(@SearchTime,@j)))

             set  @sql  =  @sql  +  N '  or (( '   +  @dateFieldName  +  N '  =  '''   +  Convert(varchar( 100 ),@dDate, 121 )  +  N ''' ) and ( ' +  @timeFieldName  +  N '  =  ' +  Convert(varchar( 100 ),@dTime)  +  N ' )) '
             set  @j  =  @j  + 1
        end        
         set  @sql  =  @sql  +  N ' ) '

         set  @sqlJoinSelect  =  @sqlJoinSelect  +   N ' , ' + @sqlTableName  + N ' . ' + @valueFieldName + N '  as  '   +  Convert(varchar( 1000 ),dbo.fGetParamValue(@searchColumn,@colID))
         set  @sqlJoinFrom  =  @sqlJoinFrom  +  N '  left join  ' +  @sqlTableName  +  N '  on #tempTime.dDate =  ' +  @sqlTableName  +  N ' . ' + @dateFieldName  +  N '  and #tempTime.dTime =  ' +  @sqlTableName  +  N ' . ' + @timeFieldName

         set  @i  =  @i  -   1
         set  @colID  =  @colID  + 1
    end

exec(@sql  +  @sqlJoinSelect  +  @sqlJoinFrom  + '  order by #tempTime.dDate,#tempTime.dTime ' )
-- print @sql  +  @sqlJoinSelect  +  @sqlJoinFrom  + '  order by #tempTime.dDate,#tempTime.dTime '

end                                

GO

其中使用三个函数，代码分别如下：
函数一：

CREATE  FUNCTION fGetParamTotal
(@param varchar( 1000 ))
RETURNS  int
AS

BEGIN

declare @ParamTotal  int
set  @ParamTotal  =   0
if  @param  =   ''
     return   0

     while  (charindex( ' , ' ,@param)  <>   0 )
    begin
         set  @ParamTotal  =  @ParamTotal  +   1
         set  @param  =  substring(@param,charindex( ' , ' ,@param) + 1 ,len(@param))
    end
return  @ParamTotal  +   1

END

函数二：

CREATE  FUNCTION fGetParamValue
(@param varchar( 1000 ),@id  int )
RETURNS varchar( 1000 )
AS

BEGIN

declare @ParamTotal  int
declare @i  int
declare @subString varchar( 1000 )
set  @ParamTotal  =  dbo.fGetParamTotal(@param)
set  @subString  =   ''
set  @i  =   1
if  @id  <=   0  
     return  @subString
if  @ParamTotal  >=  @id    
begin
     while  (@id  >  @i)
    begin
--           set  @ParamTotal  =  @ParamTotal  +   1
         set  @param  =  substring(@param,charindex( ' , ' ,@param) + 1 ,len(@param))
         set  @i  =  @i  +   1
    end
         if  (charindex( ' , ' ,@param)  =   0 )
             set  @subString  =  @param
     else
          set  @subString  =  substring(@param, 1 ,charindex( ' , ' ,@param)  -   1 )
end
return  @subString
END

函数三：

CREATE  FUNCTION fToDate(@DateTime datetime)
RETURNS varchar( 10 ) 
AS
begin
declare @Date varchar( 10 )

set  @Date  =  convert( char ( 4 ),year(@DateTime))  +   ' - '   +  convert(varchar( 2 ),month(@DateTime))  +   ' - '   +  convert(varchar( 2 ),day(@DateTime))

return  @Date
end

存储过程的参数@searchColumn 为要查询的列的字符串，@SearchTime 为要查询的时间字符串，字符串中用逗号分割数据。

执行存储过程的语句如下所示：

pSearchData 'testTable','id','dDate','dTime','value','压力1, 压力2, 压力3','2005-1-1 8:00:00,2005-1-1 9:00:00,2005-1-1 12:00:00'

转载于:https://www.cnblogs.com/pcbhyy/archive/2005/09/09/233362.html