select * from dbo.orders  group by  custid  with cube    select custid ,row_number() over(order by empid) as number from  orders    select count(*) from orders where empid >'5' and orderid in ('10248','10249')    drop table test  create table test(id int,name varchar(20),quarter int,profile int) insert into test values(1,'a',1,1000) insert into test values(1,'a',2,2000) insert into test values(1,'a',3,4000) insert into test values(1,'a',4,5000) insert into test values(2,'b',1,3000) insert into test values(2,'b',2,3500) insert into test values(2,'b',3,4200) insert into test values(2,'b',4,5500) select * from test

select id,name, [1] as "一季度", [2] as "二季度", [3] as "三季度", [4] as "四季度", [5] as "5" from test pivot ( sum(profile) for quarter in ([1],[2],[3],[4],[5]) ) as pvt CREATE TABLE [dbo].[TestRows2Columns](     [Id] [int] IDENTITY(1,1) NOT NULL,     [UserName] [nvarchar](50) NULL,     [Subject] [nvarchar](50) NULL,     [Source] [numeric](18, 0) NULL ) ON [PRIMARY] GO

--插入测试数据 INSERT INTO [TestRows2Columns] ([UserName],[Subject],[Source])     SELECT N'张三',N'语文',60  UNION ALL     SELECT N'李四',N'数学',70  UNION ALL     SELECT N'王五',N'英语',80  UNION ALL     SELECT N'王五',N'数学',75  UNION ALL     SELECT N'王五',N'语文',57  UNION ALL     SELECT N'李四',N'语文',80  UNION ALL     SELECT N'张三',N'英语',100 GO

SELECT * FROM [TestRows2Columns]

select username , sum(case[subject] when '数学' then source else  0 end) as '[数学]', sum(case[subject] when '英语' then source else  0 end) as '[英语]', sum(case[subject] when '语文' then source else  0 end) as '[语文]' from [TestRows2Columns] group by username

select * from (select username,subject,source from TestRows2Columns)p pivot (sum (source) for subject in ([数学],[英语],[语文] )) as v order by v.username

最新文章

1. spring 静态注入
2. 开发者必知的几款App快速开发工具
3. JQuery的动画及其幻灯片效果
4. Python - 升级所有已安装的第三方包
5. JAVA运算符和优先级
6. 限制<input>输入内容 只允许数字 或者 字母
7. Centos7搭建集中式日志系统
8. JQUERY 键盘事件
9. 基本调试命令 - u/ub/uf
10. Failed to issue method call: Unit mysql.service failed to load: No such file or directory解决的方式
11. 4、Hibenrate中HQL的10中查询方式
12. PHP 安装使用 gearman
13. Quick RF Tips for General Reference
14. THUSC2017题解
15. 如何移植freertos
16. 安装mysql后必做的两件事
17. 「PKUSC2018」主斗地（暴搜）
18. C# Hadoop学习笔记（二）—架构原理
19. mfc 控件添加变量
20. lvm xfs 扩容

热门文章

1. 使用delegate实现简单的查询功能
2. canvas判断边距，反弹和拖拽的综合实例
3. JVM学习之jstat使用方法
4. hadoop fs -mkdir testdata错误 提示No such file or directory
5. MySQL server PID file could not be found!
6. Elasticsearch 1.X 版本Java插件开发
7. DIRECTORY_SEPARATOR：PHP 系统分隔符常量
8. 【BZOJ-4423】Bytehattan 并查集 + 平面图转对偶图
9. 【bzoj1597】 土地购买
10. 【bzoj1014】 JSOI2008—火星人prefix