ms sql 交叉表的写法，列不固定

--最近四次体检日期的各项体检项目分析
--exec sp_LCTJFX '888888888888888'
CREATE  proc sp_LCTJFX
@HYZH varchar(20) --会员帐号
as
--显示一表 纵向为各种体检项目体检项目数据 横向为最近四次体检日期
--相关表 有 KX_TJFX(体检分析项目清单)  PhysicalDetail(体检项目明细) PhysicalBase(体检基本项目表)
set nocount on
--待执行的SQL语句
declare @sql varchar(8000)
--
declare @CreateTable_sql varchar(8000)
declare @insert_sql varchar(8000)
declare @select_sql varchar(8000)
set @CreateTable_sql=''
set @insert_sql=''
set @select_sql=''
declare @exe_sql varchar(200)
set @exe_sql = ''

--创建一个表用于存放要显示的结构  #TJFX
set @CreateTable_sql ='CREATE TABLE #TJFX(aoto_id smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED'
--体检项目
set @CreateTable_sql =@CreateTable_sql+','+CHAR(13)+'[TJXMID] varchar(50)  null ' --not 20080902去掉
set @CreateTable_sql =@CreateTable_sql+','+CHAR(13)+'[TJXM] varchar(50) not null '
set @CreateTable_sql =@CreateTable_sql+','+CHAR(13)+'[TJDW] varchar(20) null '

--将所有的体检明细插入到一临时表 #temp
set @insert_sql=' select P.ItemId, convert(varchar(10),PB.PhysicalTime,21) AS TJRQ, P.ItemName AS TJXM,P.ResultValue AS JG,P.ResultUnit AS DW into #temp '
set @insert_sql= @insert_sql + CHAR(13) +' FROM PhysicalBase PB JOIN  PhysicalDetail  P  ON (PB.PublishId=P.PublishId and PB.OrganId=P.OrganId) JOIN KX_TJDAGDB T ON (T.FBLSH=P.PublishId and T.YYBH=P.OrganId) '
set @insert_sql= @insert_sql + CHAR(13) +' WHERE HYZH=''' + @HYZH +  ''''
set @insert_sql= @insert_sql +  ' ORDER BY P.ItemId ' --and ItemName in (SELECT BT FROM KX_TJFX)

--将要统计的体检项目插入　#TJFX
--set @insert_sql= @insert_sql + CHAR(13) +'insert into #TJFX([TJXMID],[TJXM],[TJDW]) SELECT ItemId,TJXM,DW FROM #temp Group by ItemId, TJXM,DW order by ItemId'
set @insert_sql= @insert_sql + CHAR(13) +'insert into #TJFX( [TJXMID] , [TJXM],[TJDW]) SELECT MAX(ItemId) as ItemId, TJXM,DW FROM #temp Group by TJXM,DW order by ItemId ' --080902 order by ItemId
--在　#ZJ_TJRQ　中插入唯一的体检日期
CREATE TABLE #ZJ_TJRQ(aoto_id smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED,[TJRQ] datetime not null)
 INSERT INTO #ZJ_TJRQ(TJRQ) select PB.PhysicalTime FROM PhysicalBase PB JOIN  PhysicalDetail  P  ON
(PB.PublishId=P.PublishId and PB.OrganId=P.OrganId) JOIN KX_TJDAGDB T ON (T.FBLSH=P.PublishId and T.YYBH=P.OrganId)
WHERE HYZH = @HYZH group by PB.PhysicalTime

--把最近的四次体检日期　，插入到#TJFX　的列
--select * from KX_TJFX

declare @TJRQ varchar(10) --疾病编号

declare @col int
set @col=0
DECLARE TJFX_cursor CURSOR FOR
SELECT TOP 4 convert(varchar(10),TJRQ,21)  FROM #ZJ_TJRQ order by TJRQ DESC --取得最近四次体检日期
open TJFX_cursor
FETCH NEXT FROM TJFX_cursor INTO @TJRQ
WHILE @@FETCH_STATUS = 0
begin
  set @col=@col +1
  --增加临时表中的列
  set @CreateTable_sql=@CreateTable_sql+','+CHAR(13)+'[col_'+ltrim(@col)+'] VARCHAR(20)   '
 
  --新增列对应的名称
  if (@select_sql='')
    set @select_sql ='[col_'+ltrim(@col)+'] as '''+ @TJRQ +''''
  else
    set @select_sql = @select_sql + ',[col_'+ltrim(@col)+'] as '''+ @TJRQ +''''
  --更新此列的对应体检项目结果
  set @insert_sql= @insert_sql + CHAR(13)+'update #TJFX set [col_'+ltrim(@col)+'] = CAST(JG AS VARCHAR(20)) from #TJFX,#temp where #TJFX.TJXM=#temp.TJXM AND #temp.TJRQ=''' + @TJRQ +''''
 
 
  FETCH NEXT FROM TJFX_cursor INTO @TJRQ
end
--建表成功
set @CreateTable_sql = @CreateTable_sql+ ')'

set @select_sql='select TJXM as ''体检项目'' , '+@select_sql +',TJDW as ''单位'' from #TJFX'
set @sql = @CreateTable_sql
set @sql = @sql+CHAR(13) + @insert_sql
set @sql = @sql+CHAR(13) + @select_sql
set @sql = @sql+CHAR(13) + 'drop table #temp'
set @sql = @sql+CHAR(13) + 'drop table #TJFX'
--print '@CreateTable_sql: ' + @CreateTable_sql + char(13) +char(10)
--print '@insert_sql: ' + @insert_sql + char(13) +char(10)
--print '@select_sql: ' + @select_sql + char(13) +char(10)
--执行将sql语句
exec (@sql)
CLOSE TJFX_cursor
DEALLOCATE TJFX_cursor

GO