用存储过程，实列列表的列转行转换

第一，利用动态SQL实现

USE [TJSYS]
GO
/****** Object:  StoredProcedure [dbo].[GetTJResult]    Script Date: 05/30/2012 17:47:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 -- =============================================
 -- AUTHOR: <AUTHOR,,刘雪莲>
 -- CREATE DATE: <2012-05-28,>
 -- DESCRIPTION: <把体检结果表RESULT,RESULT1转变为横列输出>
 -- =============================================
 ALTER PROCEDURE [dbo].[GetTJResult]
 (
 @DWMC VARCHAR(350),--单位名称
 @BEGINTIME DATETIME,---起始日期
 @ENDTIME DATETIME---结束日期
 )
 AS
 IF OBJECT_ID('TEMPDB..#TEMPTABLE') IS NOT NULL
 DROP TABLE #TEMPTABLE
 CREATE TABLE #TEMPTABLE
 (
 TJID VARCHAR(50),--体检ID
 LRRQ VARCHAR(50),--录入日期
 DWMC VARCHAR(350),--工作单位
 XM VARCHAR(50),--姓名
 XB VARCHAR(50),--性别
 NL VARCHAR(50),--年龄
 TJXM VARCHAR(350),--体检项目编号
 XMMC VARCHAR(350),--体检项目名称
 RESULT VARCHAR(550),--体检结果
 TJREPNO VARCHAR(50)--报告单号

 )
 BEGIN
 --把按条件查询的记录填入到临时表中
 INSERT INTO #TEMPTABLE(TJID,LRRQ,DWMC,XM,XB,NL,TJXM,XMMC,RESULT,TJREPNO)
 SELECT TJRYXX.ID TJID,CONVERT(NVARCHAR,TJRYXX.LRRQ,111) LRRQ,TJRYXX.DWMC,TJRYXX.XM,
 CASE WHEN TJRYXX.XB=1 THEN '男' ELSE '女' END XB,TJRYXX.NL,TJRESULT.TJXM,TJRESULT.XMMC,TJRESULT.RESULT,
 TJREPORT.TJREPNO FROM TJSYS_TJRYXX TJRYXX LEFT JOIN TJSYS_TJREPORT TJREPORT ON TJRYXX.ID=TJREPORT.TJID INNER JOIN TJSYS_TJRESULT
 TJRESULT ON TJRESULT.TJREPNO = TJREPORT.TJREPNO WHERE
 TJRYXX.DWMC = @DWMC AND TJRYXX.LRRQ >= @BEGINTIME AND TJRYXX.LRRQ < @ENDTIME
 --SELECT * FROM #TEMPTABLE
 --对取得的结果数据进行行列转换，把行转换为列
 DECLARE @SQL VARCHAR(4000)
 DECLARE @RESULT VARCHAR(4000)
 SET @SQL='SELECT TJID,MIN(LRRQ) 录入日期,MIN(DWMC) 工作单位,MIN(XM) 姓名,MIN(XB) 性别,MIN(NL) 年龄'
 SELECT
 @SQL=@SQL+
 ',ISNULL(MAX(CASE XMMC WHEN'''+XMMC+'''THEN RESULT END),NULL)['+XMMC+']'
 FROM (SELECT DISTINCT XMMC FROM #TEMPTABLE) AS A
 SELECT @SQL=@SQL+'FROM #TEMPTABLE GROUP BY TJID'
 --PRINT @SQL
 IF OBJECT_ID('TEMPDB..##RESULTTEMP') IS NOT NULL
 DROP TABLE ##RESULTTEMP
 SELECT @RESULT='SELECT * INTO ##RESULTTEMP FROM ('+@SQL+') C'
 EXEC(@RESULT)
 --SELECT * FROM ##RESULTTEMP
 --从临时表中取出数据，把RESULT1中的结果集列转为横向后进行合并
 --1,如果TJSYS_TJRESULT1中有数据，则进行转换，把列转为行
 IF OBJECT_ID('TEMPDB..#TEMPTABLE2') IS NOT NULL
 DROP TABLE #TEMPTABLE2
 CREATE TABLE #TEMPTABLE2
 (
 TJID VARCHAR(50),--体检ID
 TJKS VARCHAR(50),--体检科室
 JCJL VARCHAR(500),--检查结论
 KSMC VARCHAR(500),--科室名称
 TJZJ VARCHAR(500),--体检总结
 TJJY VARCHAR(500)--体检建议
 )
 --向临时表中插入记录
 INSERT INTO #TEMPTABLE2(TJID,TJKS,JCJL,KSMC,TJZJ,TJJY)
 SELECT TJRYXX.ID TJID, TJREPORT.TJKS,TJRESULT1.JCJL,TJKS.KSMC,TJZJ.TJZJ,TJZJ.TJJY FROM TJSYS_TJRYXX TJRYXX
 LEFT JOIN TJSYS_TJREPORT TJREPORT ON TJRYXX.ID=TJREPORT.TJID RIGHT JOIN
 TJSYS_TJRESULT1 TJRESULT1 ON TJRESULT1.TJREPNO = TJREPORT.TJREPNO LEFT JOIN TJSYS_TJZJ TJZJ
 ON TJRYXX.ID=TJZJ.TJID LEFT JOIN TJSYS_TJKS TJKS ON TJREPORT.TJKS=TJKS.KSBH WHERE
 TJRYXX.DWMC = @DWMC AND TJRYXX.LRRQ >= @BEGINTIME AND TJRYXX.LRRQ < @ENDTIME
 --SELECT * FROM #TEMPTABLE2
 --把列转变这横向
 DECLARE @SQL2 VARCHAR(4000)
 DECLARE @RESULT2 VARCHAR(4000)
 SET @SQL2='SELECT TJID'
 SELECT
 @SQL2=@SQL2+
 ',ISNULL(MAX(CASE KSMC WHEN'''+KSMC+'''THEN JCJL END),NULL)['+KSMC+']'
 FROM (SELECT DISTINCT KSMC FROM #TEMPTABLE2) AS A
 SELECT @SQL2=@SQL2+', MIN(TJZJ) 体检总结,MIN(TJJY) 体检建议 ' +'FROM #TEMPTABLE2 GROUP BY TJID'
 SELECT @RESULT2='SELECT * FROM ##RESULTTEMP A LEFT JOIN ('+@SQL2+') B ON A.TJID=B.TJID'

 EXEC

(@RESULT2);

 END

 

第二，利用pivot函数实现

USE [TJSYS]
GO
/****** Object:  StoredProcedure [dbo].[SubKSStatistic]    Script Date: 05/30/2012 17:48:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 -- =============================================
 -- AUTHOR: <AUTHOR,,刘雪莲>
 -- CREATE DATE: <CREATE DATE,,2012-05-30>
 -- DESCRIPTION: <DESCRIPTION,,分科室统计>
 --特殊说明：根据跟踪老系统的结果，特检检查的金额为心电图检查科与内科检查科项目金额的类加
  --备注：心电图检查科与内科检查科指的是科室类别是这两项，而并非科室名称
 -- =============================================
 ALTER PROCEDURE [dbo].[SubKSStatistic]
 (
 @BEGINTIME DATETIME,---起始日期
 @ENDTIME DATETIME---结束日期
 )
 AS
 IF OBJECT_ID('TEMPDB..#TEMPTABLE') IS NOT NULL
 DROP TABLE #TEMPTABLE
 CREATE TABLE #TEMPTABLE
 (
 jcks VARCHAR(50),--检查科室
 xmje decimal(18,2),--项目金额
 skrq VARCHAR(350)--收款日期
 )
 BEGIN
 --查询出指定时间段的收费明细表，填入到临时表中
 INSERT INTO #TEMPTABLE(jcks,xmje,skrq)
 SELECT TJKSCLASS.KSNAME,TJSKMX.XMJE,CONVERT(NVARCHAR(122),TJSKMX.SKRQ,111) AS SKRQ
 FROM TJSYS_TJSKMX TJSKMX,TJSYS_TJITEM TJITEM,TJSYS_TJKS TJKS,TJSYS_TJKSCLASS TJKSCLASS WHERE ( TJSKMX.XMBH = TJITEM.XMBH )
 AND ( TJITEM.KSBH = TJKS.KSBH AND TJKS.JCSK=TJKSCLASS.JCKS) AND ( TJSKMX.SKRQ >=@BEGINTIME AND
 TJSKMX.SKRQ < @ENDTIME)
    --把列转为行输出
 SELECT SKRQ 收款日期,isnull([检验科],0)[检验科],isnull([放射科],0)[放射科],(isnull([心电图室],0)+isnull([内科],0)) AS 特检,
 isnull([体检科],0)[体检科],(isnull([检验科],0)+isnull([放射科],0)+isnull([心电图室],0)+isnull([内科],0)+isnull([体检科],0)) AS 小计
 FROM #TEMPTABLE AS A
 PIVOT
 (
  SUM(xmje) FOR jcks IN ([检验科],[放射科],[心电图室],[内科],[体检科])
 )
 AS B

 END

第三，利用SQL语句实现

SELECT     SKRQ, SUM(shenghua) AS shenghua, SUM(nfenxi) AS nfenxi, SUM(xuechanggui) AS xuechanggui, SUM(CR) AS CR, SUM(xiongtou) AS xiongtou, SUM(xindiantu)
                      AS xindiantu, SUM(caichao) AS caichao, SUM(dmyh) AS dmyh, SUM(gumidu) AS gumidu, SUM(other) AS other, SUM(xiaoji) AS xiaoji
FROM         (SELECT     SKRQ, (CASE sfks WHEN '01' THEN SUM(xmje) ELSE 0 END) AS shenghua, (CASE sfks WHEN '02' THEN SUM(xmje) ELSE 0 END) AS nfenxi,
                                              (CASE sfks WHEN '04' THEN SUM(xmje) ELSE 0 END) AS xuechanggui, (CASE sfks WHEN '34' THEN SUM(xmje) ELSE 0 END) AS CR,
                                              (CASE sfks WHEN '35' THEN SUM(xmje) ELSE 0 END) AS xiongtou, (CASE sfks WHEN '08' THEN SUM(xmje) ELSE 0 END) AS xindiantu,
                                              (CASE sfks WHEN '09' THEN SUM(xmje) ELSE 0 END) AS caichao, (CASE sfks WHEN '19' THEN SUM(xmje) ELSE 0 END) AS dmyh,
                                              (CASE sfks WHEN '20' THEN SUM(xmje) ELSE 0 END) AS gumidu, (CASE WHEN sfks NOT IN ('01', '02', '04', '34', '35', '08', '09', '19', '20') OR
                                              sfks IS NULL THEN SUM(xmje) ELSE 0 END) AS other, SUM(XMJE) AS xiaoji
                       FROM          dbo.view_subtjskmx
                       GROUP BY SKRQ, sfks) AS a
GROUP BY SKRQ
HAVING      (COUNT(SKRQ) > 0)