MSSQL将查询结果横向显示
来源:互联网 发布:域名投资技巧pdf 编辑:程序博客网 时间:2024/05/17 02:37
表1year_month,amount2008015200802620080392008024显示结果YEAR010203TOT20085005200801001020080099200800024可能多个年,多个月
CREATE PROCEDURE testpp
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
create table testaa
(
year_month int,
amount int
)
insert into testaa values (200801,5)
insert into testaa values (200802,6)
insert into testaa values (200803,9)
insert into testaa values (200804,4)
declare @sql nvarchar(4000)
declare @s_st nvarchar(2)
declare @s_y nvarchar(4)
declare @s_m nvarchar(2)
declare @s_name nvarchar(300)
set @s_name=''
set @sql='create table testaa2(year1 int'
declare cc cursor for select substring(convert(nvarchar(6),year_month),5,2) from testaa group by substring(convert(nvarchar(6),year_month),5,2)
open cc
fetch next from cc into @s_st
while @@FETCH_STATUS =0
begin
set @sql=@sql+',m'+@s_st+' int default 0'
set @s_name=@s_name+'m'+@s_st+'+'
print @s_name
FETCH NEXT FROM cc into @s_st
end
close cc
DEALLOCATE cc
set @sql=@sql+', tot int default 0)'
print @sql
exec sp_executesql @sql
fetch next from cc into @s_st
while @@FETCH_STATUS =0
begin
set @sql=@sql+',m'+@s_st+' int default 0'
set @s_name=@s_name+'m'+@s_st+'+'
print @s_name
FETCH NEXT FROM cc into @s_st
end
close cc
DEALLOCATE cc
set @sql=@sql+', tot int default 0)'
print @sql
exec sp_executesql @sql
declare cc cursor for select substring(convert(nvarchar(6),year_month),1,4),substring(convert(nvarchar(6),year_month),5,2) from testaa group by year_month
open cc
fetch next from cc into @s_y,@s_m
while @@FETCH_STATUS =0
begin
set @sql='insert into testaa2 (year1,m'+@s_m+') select convert(int,substring(convert(char(6),year_month),1,4)),sum(amount) from testaa where year_month='+@s_y+@s_m+' group by year_month'
print @sql
exec sp_executesql @sql
FETCH NEXT FROM cc into @s_y,@s_m
end
close cc
DEALLOCATE cc
fetch next from cc into @s_y,@s_m
while @@FETCH_STATUS =0
begin
set @sql='insert into testaa2 (year1,m'+@s_m+') select convert(int,substring(convert(char(6),year_month),1,4)),sum(amount) from testaa where year_month='+@s_y+@s_m+' group by year_month'
print @sql
exec sp_executesql @sql
FETCH NEXT FROM cc into @s_y,@s_m
end
close cc
DEALLOCATE cc
print @s_name
set @sql='update testaa2 set tot='+substring(@s_name,1,len(@s_name)-1)
print @sql
exec sp_executesql @sql
select * from testaa2
drop table testaa2
END
GO
- MSSQL将查询结果横向显示
- oracle 将查询结果纵向横向显示。
- [MSSQL]SQL 查询结果的纵向变横向排列
- 横向合并查询结果
- oracle 查询结果横向union
- SQL 分组查询,把纵向显示的结果变成横向显示
- Sql分组查询,如何把纵向显示的结果变成横向显示
- Sql分组查询,如何把纵向显示的结果变成横向显示
- MySQL如何将select子查询结果横向拼接后插入数据表中
- [MSSQL]将查询结果赋值给变量时使用set与select的区别
- 怎么将查询的结果集显示的很整齐
- 查询显示MSSQL表结构
- 给MSSQL查询结果追加行号
- mssql 为查询结果增加编号
- 如何把纵向显示的结果变成横向显示?
- 将数据表的纵向数据横向显示
- SQL查询语句行转列横向显示
- 将mysql查询结果导出
- 完美js限制文本框只能输入数字
- 根据年月日获得星期几
- 安装Oracle10g for RHEL5
- 内森主宰伦敦金融城
- 内核驱动主要数据结构
- MSSQL将查询结果横向显示
- 这两天在弄JAVASCRIPT
- HashMap和Hashtable的区别
- 学习C++:实践者的方法(Beta1)
- 财富人物介绍
- putty ubuntu tomcat 文件传输
- 关于需求分析的一个笑话
- 层的相对位置-relative
- A simple kernel module for linux FC4(kernel version 2.6.11-1.1369_FC4smp)