最近要统计一张报表数据量不是很大,就写了一个存取过程。
1.#tempData1表结构如下
xm grbjh dxmmc je
陈XX 34000000003000120101130143257373 检查费 10.0000
陈XX 34000000003000120101201153826560 挂号费 0.0000
陈XX 34000000003000120101201153826560 手术费 70.0000
陈XX 34000000003000120101201153826560 西药费 32.3000
陈XX 34000000003000120101203152458543 挂号费 0.0000
2.想要转化的结果表结构#tmp
姓名 个人保健号 检查费 材料费 .................
省略的列的个数与#tempData1的dxmmc的种类相同
将表一转换为表二
以下是存取过程的内容:
create PROCEDURE usp_translateTable_get
@DWDM varchar(20), --单位代码
@CARDTYPE int, --病人类型
@KSRQ varchar(20), --开始日期
@JSRQ varchar(20), --结束日期
as
CREATE TABLE #tmp(姓名 varchar(20),个人保健号 varchar(50))
SELECT a.xm,a.grbjh, b.sfxmflmc AS dxmmc,sum(isnull(b.je,0)) as je
into #tempData1
from mz_sfzb a(nolock),mz_sfmx b(nolock)
where a.wssdm = b.wssdm
and a.id = b.zb_id
AND a.cardtype LIKE CONVERT(VARCHAR(2), @CARDTYPE)+'%'
and a.wssdm = @DWDM
and datediff(minute,a.sfsj,@KSRQ)<=0
and datediff(minute,a.sfsj,@JSRQ)>0
and sfbz not in ('0','9') --sfbz 0:未收费1:收费2:红冲3:被红冲9:作废
group by a.xm,a.grbjh,b.sfxmflmc
SELECT DISTINCT(dxmmc)
INTO #tempData2
FROM #tempData1
DECLARE @sql VARCHAR(4000)
SET @sql=' '
SELECT @sql=@sql + ' ALTER TABLE #tmp ADD '+ dxmmc+ ' decimal(14,4) null default 0'
FROM #tempData2
SELECT @sql=@sql + ' ALTER TABLE #tmp ADD 小计 decimal(14,4) null default 0'
exec (@sql)
INSERT INTO #tmp(个人保健号,姓名)
SELECT distinct grbjh AS 个人保健号,xm as 姓名
FROM #tempData1
Declare @colName VARCHAR(40),@colCount INT,@updateSql VARCHAR(1000)
SELECT * FROM #tempData1
SELECT * FROM #tmp
SELECT @colCount =COUNT(dxmmc) FROM #tempData2
WHILE @colCount>0
BEGIN
SELECT @colName = dxmmc FROM (SELECT ROW_NUMBER() OVER (order by dxmmc)as RowNumber,
dxmmc from #tempData2) as tmpTable WHERE RowNumber=@colCount
set @colCount=@colCount-1
set @updateSql=' UPDATE a SET a.'+@colName+'=b.je FROM #tmp a,#tempData1 b WHERE
a.个人保健号= b.grbjh AND
a.姓名=b.xm and
b.dxmmc='''+@colName+''''
EXEC (@updateSql)
END
UPDATE a SET a.小计= b.je
from #tmp a,(select grbjh,xm,sum(je)AS je from #tempData1 GROUP BY grbjh,xm) b
WHERE
a.个人保健号= b.grbjh AND
a.姓名=b.xm
SELECT * FROM #tmp
DROP table #tmp
上述实现方法有些地方效率欠缺考虑,数据量不大的情况下一般可满足应用。