sqlserver表分区与调优与行列转换

时间：2022-11-15 07:29:06

转自：

http://www.cnblogs.com/knowledgesea/p/3696912.html

http://www.open-open.com/lib/view/open1418462880292.html

http://blog.jobbole.com/55086/

http://blog.csdn.net/dj2008/article/details/10112717

http://tech.it168.com/a2009/1125/814/000000814758_all.shtml

http://blog.csdn.net/dinglang_2009/article/details/20404323

http://tech.it168.com/a2012/0425/1341/000001341794_all.shtml

http://www.cnblogs.com/ulex/archive/2009/12/04/1617016.html

分区表简介

http://www.cnblogs.com/kissdodog/p/3156758.html

SqlServer 自动化分区方案

http://www.cnblogs.com/tyb1222/p/3388438.html

千万级SQL Server数据库表分区的实现

http://database.51cto.com/art/201009/225448.htm

http://kb.cnblogs.com/page/173339/

http://jingyan.baidu.com/article/6b97984d9a26ec1ca3b0bf77.html

你真的会玩SQL吗？玩爆你的数据报表之存储过程编写（下）

http://www.cnblogs.com/zhangs1986/p/4972400.html

从表#tempSaleDtl2行转列，按类型聚合 求出每个产品每个类型(面积、金额……)的合计 放入表#tempSaleDtl3

sqlserver表分区与调优与行列转换
SELECT ProductGUID,type,typecode,

    MAX(CASE YearMonth WHEN '9999-13' THEN val ELSE 0 END) AS '项目合计',

    MAX(CASE YearMonth WHEN @Year+'-00' THEN val ELSE 0 END) AS '以前年度合计',

    MAX(CASE YearMonth WHEN @Year+'-13' THEN val ELSE 0 END) AS '2011年合计',

    MAX(CASE YearMonth WHEN @Year+'-01' THEN val ELSE 0 END) AS '2011-01',

    MAX(CASE YearMonth WHEN @Year+'-02' THEN val ELSE 0 END) AS '2011-02',

    MAX(CASE YearMonth WHEN @Year+'-03' THEN val ELSE 0 END) AS '2011-03',

    MAX(CASE YearMonth WHEN @Year+'-04' THEN val ELSE 0 END) AS '2011-04',

    MAX(CASE YearMonth WHEN @Year+'-05' THEN val ELSE 0 END) AS '2011-05',

    MAX(CASE YearMonth WHEN @Year+'-06' THEN val ELSE 0 END) AS '2011-06',

    MAX(CASE YearMonth WHEN @Year+'-07' THEN val ELSE 0 END) AS '2011-07',

    MAX(CASE YearMonth WHEN @Year+'-08' THEN val ELSE 0 END) AS '2011-08',

    MAX(CASE YearMonth WHEN @Year+'-09' THEN val ELSE 0 END) AS '2011-09',

    MAX(CASE YearMonth WHEN @Year+'-10' THEN val ELSE 0 END) AS '2011-10',

    MAX(CASE YearMonth WHEN @Year+'-11' THEN val ELSE 0 END) AS '2011-11',

    MAX(CASE YearMonth WHEN @Year+'-12' THEN val ELSE 0 END) AS '2011-12',

    MAX(CASE YearMonth WHEN '9999-12' THEN val ELSE 0 END) AS '以后年度合计'

into #tempSaleDtl3

FROM #tempSaleDtl2

GROUP BY ProductGUID,type,typecode

ORDER BY ProductGUID,typecode
sqlserver表分区与调优与行列转换

来看看这里的数据与前一步的对应关系：

sqlserver表分区与调优与行列转换

结果部分数据如图：

sqlserver表分区与调优与行列转换

其中列名为了显示方便，这里用了2011，可以将列名变了year-01……。

关于行转列的知识前系列也提过，不理解的请自觉前去复习。

小技巧是用到了MAX聚合，关于这点前面聚合的文章中有提到。整个的数据结构现在越来越趋近于最后的结果了，year~继续

SQL Server中行列转换 Pivot UnPivot

http://www.cnblogs.com/zhangzt/archive/2010/07/29/1787825.html

http://www.cnblogs.com/gaizai/p/3753296.html

http://www.cnblogs.com/barrysgy/archive/2010/11/19/1881596.html

create table #aa
(
name nvarchar(20),
kecheng nvarchar(20),
fengshu int
)

insert #aa
select '张三','语文',56
union all
select '张三','数学',60
union all
select '张三','英语',70
union all
select '李四','语文',55
union all
select '李四','物理',82

select * from #aa

select name,
MAX(case kecheng when '语文' then fengshu end ) AS '语文',
MAX(case kecheng when '数学' then fengshu end ) AS '数学',
MAX(case kecheng when '英语' then fengshu end ) AS '英语',
MAX(case kecheng when '物理' then fengshu end ) AS '物理'
from #aa group by name

SqlServer2008 数据库同步的两种方式(发布、订阅使用方法)

http://www.jb51.net/article/53505.htm

http://www.cnblogs.com/nzperfect/archive/2013/01/09/2852377.html

相关文章