横纵转换SQL

CREATE procedure [dbo].[spr_VerticalHhorizontalConversion] 
(
    @table_to_turn varchar(255), --待旋转的表
    @AutoID varchar(255), --保留的关键字段
    @key_col varchar(255), --保留的关键字段
    @key_col_date varchar(255), --保留的关键日期字段
    @col_know_how varchar(255), --生成列名的字段
    @col_to_turn1 varchar(255), --作为值的字段1
    @col_to_turn2 varchar(255) --作为值的字段2。只有当@col_to_turn1没有值时才取@col_to_turn2的值
) 
/* tVerticalHhorizontal_TMP
过程作用，根据纵向数据生成新横向结构
by realgz@csdn 2003-12-26 
*/ 
as
declare @exec varchar(8000) 

create table #tmp (col varchar(255)) 
set @exec= 'SELECT DISTINCT '+@col_know_how+ ' FROM '+@table_to_turn 

insert into #tmp 
exec (@exec) 

set @exec= ' ' 
select @exec=@exec+ '(CASE WHEN  '+@col_know_how+ '= '''+col+ ''' THEN  ISNULL('+@col_to_turn1 +','+@col_to_turn2
        + ') ELSE NULL END ) AS  '''+col+ ''', ' 
from 
#tmp 
set @exec=left(@exec,len(@exec)-1) 

set @exec= 'SELECT  ' + @key_col+ ' AS 产成品序列号, ' + @AutoID + ' AS _AutoID, '+@key_col_date + ' AS Time, ' + @exec+ ' FROM  '+@table_to_turn+ ' GROUP BY  '+@key_col+ ',' 
        +@AutoID+','+@key_col_date + ',' + @col_know_how+',' + @col_to_turn1 + ',' + @col_to_turn2 
        +' ORDER BY '+@key_col+' DESC '
exec(@exec)