[MSSQL]SQL 查询结果的纵向变横向排列

/*create table BookLibary([图书馆] varchar(10), [科目] varchar(10), [数量] int) insert into BookLibary select 'A', '语文', 5 union all select 'B', '数学', 6 union all select 'C', '英语', 3 union all select 'B', '语文', 4 union all select 'A', '数学', 3 */ declare @exec varchar(8000) set @exec='select [图书馆]' select @exec=@exec+',max(case when [科目] ='''+ [科目] +''' then [数量] else '''' end) as '''+[科目]+'''' from BookLibary group by [科目] set @exec=@exec+'from BookLibary group by [图书馆]' print @exec exec(@exec) select * from BookLibary ------------------------------- 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 + ', ' + @vSumOrCount + '(' + @vSumColName + ') as ' + @vNum + ' from ' + @vTable if(@vClause <> '') set @sql = @sql + ' where ' + @vClause set @sql = @sql + ' group by ' + @vColumns + ', ' + @vColumnName set @sql = @sql + ') as casual_table group by ' + @vColumns + ' ' if(@vOrderby <> '') set @sql = @sql + 'order by ' + @vOrderby print @sql exec sp_executesql @sql with recompile /**//* * sql语句 引号之内的为sql中的几个参数分别为（分组的列，条件，排序，取出行数） * exec Model_his_mid 'kjkm','date = ''8''','kjkm desc','' */ /**//* * sql语句 * exec Model_his_mid 'kjkm','','kjkm desc','15' */