/*
* 針對縱向轉橫向的模型
*/
GO
drop procedure Model_his_mid;
GO
create procedure Model_his_mid(
/*
* @vColumns 分組的列 (最後一個字段後面一定不要加,)
* @vClause 查詢表的條件(沒有條件='')
* @vOrderby 查詢結果排序的字段 ''為默認排序
* @vTop 取出的行數 ''為全部
*/
@vColumns nvarchar(1000),
@vClause nvarchar(4000),
@vOrderby nvarchar(100),
@vTop nvarchar(50)
)
as
declare @sql nvarchar(4000)
declare @xColumns nvarchar(4000)
declare @vTable nvarchar(50)
declare @vColumnName nvarchar(100)
declare @vSumColName nvarchar(100)
declare @vNum nvarchar(50)
declare @vAllNum nvarchar(50)
declare @vSumOrCount nvarchar(50)
set @xColumns = ''
/*
* 需要賦值的參數
*
* @vTable 真正查詢的表名
* @vColumnName 縱向轉橫向的列名
* @vSumColName 取合計值的列名
* @vNum 數值的別名
* @vAllNum 每行合計的列名 (如果不要合計則='')
* @vSumOrCount sum或者count
*
*/
set @vTable = 'his_mid_tab'
set @vColumnName = 'hsks'
set @vSumColName = 'qty'
set @vNum = 'num'
set @vAllNum = 'allnum'
set @vSumOrCount = 'sum'
select
@xColumns = @xColumns
+ 'sum(case ' + @vColumnName + ' when '+ char(39) + [a].[column] + char(39)+ ' then ' + @vNum + ' else 0 end) as '''
+ [a].[column] + ''', '
/*
* 需要比例加入此語句
* + '(' + 'cast(round(sum(case ' + @vColumnName + ' when '+ char(39) + [a].[column] + char(39)+ ' then ' + @vNum + ' else 0 end)*100.00' + '/sum(' + @vNum + '),2) as decimal(10,2))) as ' + [a].[column] + '_i, '
*/
/**
* 要動態顯示列的sql語句 住院 OUTP_OR_INP = '0' 門診OUTP_OR_INP = '1'
*/
from (select distinct DEPT_NAME as [column] from his_mzkm_tab where OUTP_OR_INP = '0') as a
set @xColumns = substring(@xColumns, 0, len(@xColumns))
set @sql = 'select '
if(@vTop <> '')
set @sql = @sql + 'top ' + @vTop + ' '
set @sql = @sql + @vColumns + ', ' + @xColumns
if(@vAllNum <> '')
set @sql = @sql + ', sum(' + @vNum + ') as ' + @vAllNum
set @sql = @sql + ' from ('
+ 'select ' + @vColumns + ', ' + @vColumnName + ',
