SAV_P_SELSQL'select * from SAV_IO_部门信息 group by 部门名称,部门ID,排序','部门名称,部门ID,排序'
查询结果
存储过程代码
CREATEPROCEDURE[dbo].[SAV_P_SELSQL](-- 任意SQL 但是不能包含order by@d_sqlvarchar(max),-- 用于order by 排序 列名@f_sqlvarchar(max))Asdeclare@c_Sqlasvarchar(max)declare@r_Sqlasvarchar(max)declare@cTmp1asvarchar(max)declare@c_RowNameasvarchar(max)set@c_RowName=''Set@c_Sql=''set@r_Sql='select * into ##sav_a from ('+@d_sql+') as a order by '+@f_sql--将查询结果缓存到全局变量中exec(@r_Sql)select*into#sav_a from ##sav_adroptable##sav_a--从全局变量中获取临时表的列名select name into#b from tempdb.dbo.syscolumns where id=object_id('tempdb..#sav_a') Beginselect name into#CowA from #b Declare my_cursor cursor scroll dynamic forselect name from#CowA open my_cursor
fetchnextfrom my_cursor into@cTmp1while(@@fetch_status=0)beginif@cTmp1isnotnullBegin--拼接查询的列名set@c_Sql=@c_Sql+',casT('+@cTmp1+' as varchar(Max))'--拼接首行列名用于union allset@c_RowName=@c_RowName+''''+','+''''+@cTmp1+''''+'AS'+''''+@cTmp1fetchnextfrom my_cursor into@cTmp1endendclose my_cursor
deallocate my_cursor
set@c_sql='Select '+substring(@c_sql,2,len(@c_sql))+' FROM #sav_a'set@c_sql='select '+ substring(@c_RowName,3,len(@c_RowName))+''''+'unionall '+@c_sqlExec(@c_sql)droptable#CowAdroptable#sav_adroptable#bEnd
GO