行转列是吗
CREATE PROCEDURE PD_RowToColumn
@DatasetSql varchar(8000),-- --要行转列的sql语句
@casedata varchar(8000),-- --要行转列的字段
@CaseColumnValue varchar(8000),-- --行转列后得到的列
@Caseelse
Value varchar(8000),-- --要行转列的else
值
@OutputColumn varchar(8000),-- --其它要输出的列
@RowOrder varchar(8000) =@casedata,-- --行转列的排序，只能是一个字段
@OrderBy varchar(10)=' ASC',
@Function varchar(15)='Max'
AS
declare @sqlstr varchar(8000)--最后的sql语句
declare @sqlstr2 varchar(8000),@head varchar(8000)--最后的sql语句
declare @RowtoColumnCursor varchar(8000)--游标
declare @CaseValue varchar(8000)--要行转列的值
declare @CursorOthervalue varchar(8000)--游标输出其它值
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TempTable]
set @DatasetSql='select * into TempTable from('+@DatasetSql+')a'
--insert into a values(@DatasetSql)
exec (@DatasetSql)
if not exists(select 1 from TempTable ) return 1000
SET @RowtoColumnCursor='declare RowtoColumnCursor scroll cursor '+char(13)+' for '+char(13)+'select distinct '+ @casedata+','+@RowOrder +' from TempTable ORDER BY '+@RowOrder+@OrderBy
--insert into a values(@RowtoColumnCursor)
exec (@RowtoColumnCursor)
open RowtoColumnCursor
set @sqlstr=''
set @sqlstr2=''
set @head=''
fetch first from RowtoColumnCursor into @CaseValue,@CursorOthervalue
while @@fetch_status =0
begin
if len(@sqlstr)<7500
set @sqlstr=@sqlstr+','+ @Function+'(Case '+@casedata+' When '''+@CaseValue + ''' then
'+ @CaseColumnValue+' else
'+@Caseelse
Value +' end) as [' +@CaseValue+']'+char(13)
else
begin
set @sqlstr2=@sqlstr2+','+@Function+'(Case '+@casedata+' When '''+@CaseValue + ''' then
'+ @CaseColumnValue+' else
'+@Caseelse
Value +' end) as [' +@CaseValue+']'+char(13)
set @head=@head+',[' +@CaseValue+']'
end
fetch next from RowtoColumnCursor into @CaseValue,@CursorOthervalue
end
CLOSE RowtoColumnCursor
DEALLOCATE RowtoColumnCursor
if len(@sqlstr2)=0
exec('SELECT '+@OutputColumn+@sqlstr+ 'from TempTable GROUP BY '+@OutputColumn+ ' ORDER BY '+@OutputColumn)
else
begin
exec('SELECT '+@OutputColumn+@sqlstr+@sqlstr2+'from TempTable GROUP BY '+@OutputColumn+ ' ORDER BY '+@OutputColumn)
end
GO
曾经写的一个公用过程，不知对你是否有用
