select 订单号,付款时间,
sum(case when 付款类别名称='现金' then 金额 else 0 end) 现金,
sum(case when 付款类别名称='银行刷卡' then 金额 else 0 end) 银行刷卡,
sum(case when 付款类别名称='现金券' then 金额 else 0 end) 现金券,
sum(case when 付款类别名称='免单' then 金额 else 0 end) 免单,
sum(case when 付款类别名称='订金' then 金额 else 0 end) 订金,
sum(case when 付款类别名称='挂房帐' then 金额 else 0 end) 挂房帐,
sum(case when 付款类别名称='欠款' then 金额 else 0 end) 欠款,
sum(case when 付款类别名称='员工垫付' then 金额 else 0 end) 员工垫付,
sum(case when 付款类别名称='会员卡' then 金额 else 0 end) 会员卡,
sum(case when 付款类别名称='内部消费' then 金额 else 0 end) 内部消费,
sum(case when 付款类别名称='外币卡' then 金额 else 0 end) 外币卡
from dbo.View_订单结款明细
group by 订单号,付款时间
select 订单号,付款时间,sum(现金) 现金,sum(银行刷卡) 银行刷卡,sum(现金券) 现金券,sum(免单) 免单,sum(订金) 订金,sum(挂房帐) 挂房帐,
sum(欠款) 欠款,sum(员工垫付) 员工垫付,sum(会员卡) 会员卡,sum(内部消费) 内部消费,sum(外币卡) 外币卡 from
(select 订单号,付款时间,现金,银行刷卡,现金券,免单,订金,挂房帐,欠款,员工垫付,会员卡,内部消费,外币卡 from View_订单结款明细
pivot(sum(金额) for 付款类别名称 in (现金,银行刷卡,现金券,免单,订金,挂房帐,欠款,员工垫付,会员卡,内部消费,外币卡)) t) a
group by 订单号,付款时间
SQL语句实现横排 2
select 订单号,付款时间,
sum(case when 付款类别名称='现金' then 金额 else 0 end) 现金,
sum(case when 付款类别名称='银行刷卡' then 金额 else 0 end) 银行刷卡,
sum(case when 付款类别名称='现金券' then 金额 else 0 end) 现金券,
sum(case when 付款类别名称='免单' then 金额 else 0 end) 免单,
sum(case when 付款类别名称='订金' then 金额 else 0 end) 订金,
sum(case when 付款类别名称='挂房帐' then 金额 else 0 end) 挂房帐,
sum(case when 付款类别名称='欠款' then 金额 else 0 end) 欠款,
sum(case when 付款类别名称='员工垫付' then 金额 else 0 end) 员工垫付,
sum(case when 付款类别名称='会员卡' then 金额 else 0 end) 会员卡,
sum(case when 付款类别名称='内部消费' then 金额 else 0 end) 内部消费,
sum(case when 付款类别名称='外币卡' then 金额 else 0 end) 外币卡
from dbo.View_订单结款明细
group by 订单号,付款时间
select 订单号,付款时间,sum(现金) 现金,sum(银行刷卡) 银行刷卡,sum(现金券) 现金券,sum(免单) 免单,sum(订金) 订金,sum(挂房帐) 挂房帐,
sum(欠款) 欠款,sum(员工垫付) 员工垫付,sum(会员卡) 会员卡,sum(内部消费) 内部消费,sum(外币卡) 外币卡 from
(select 订单号,付款时间,现金,银行刷卡,现金券,免单,订金,挂房帐,欠款,员工垫付,会员卡,内部消费,外币卡 from View_订单结款明细
pivot(sum(金额) for 付款类别名称 in (现金,银行刷卡,现金券,免单,订金,挂房帐,欠款,员工垫付,会员卡,内部消费,外币卡)) t) a
group by 订单号,付款时间
原文:http://www.cnblogs.com/crazywill/archive/2006/04/14/374951.html
前一篇文章
SQL语句实现横排 介绍了在SQL SERVER 2000中实现数据横排的功能,这篇介绍在最新的SQLSERVER 2005中的实现方法。
SQL SERVER 2005中新增加了两个关系运算符 PIVOT/ UNPIVOT,能够实现表中的列转换到行,以及行到列的转换工作。
举例,还是先创建测试数据表
我们想要得到类似这样的结果:
Year Jan Feb Mar ..............
----- ---------- ----------- -----------
2004 789.0000 389.0000 8867.0000 .............
2005 7.0000 6868.0000 688.0000 ..............
用 上一篇文章介绍的方法当然可以实现,但现在这里想要的列是固定的,不是动态的,就是12个月,所以也可以这样子来用:
但这样事实上还是相当麻烦的,现在SQLSERVER2005中有更方便的实现方法。
就是这样,很简单的用法,效果是完全一样的。
我们再尝试一下把year去掉:
SQL SERVER 2005中新增加了两个关系运算符 PIVOT/ UNPIVOT,能够实现表中的列转换到行,以及行到列的转换工作。
举例,还是先创建测试数据表
CREATE
TABLE
sales.salesByMonth
(
year char ( 4 ),
month char ( 3 ),
amount money ,
PRIMARY KEY ( year , month )
)
INSERT INTO sales.salesByMonth ( year , month , amount)
VALUES ( ' 2004 ' , ' Jan ' , 789.0000 )
INSERT INTO sales.salesByMonth ( year , month , amount)
VALUES ( ' 2004 ' , ' Feb ' , 389.0000 )
INSERT INTO sales.salesByMonth ( year , month , amount)
VALUES ( ' 2004 ' , ' Mar ' , 8867.0000 )
INSERT INTO sales.salesByMonth ( year , month , amount)
VALUES ( ' 2004 ' , ' Apr ' , 778.0000 )
INSERT INTO sales.salesByMonth ( year , month , amount)
VALUES ( ' 2004 ' , ' May ' , 78.0000 )
INSERT INTO sales.salesByMonth ( year , month , amount)
VALUES ( ' 2004 ' , ' Jun ' , 9.0000 )
INSERT INTO sales.salesByMonth ( year , month , amount)
VALUES ( ' 2004 ' , ' Jul ' , 987.0000 )
INSERT INTO sales.salesByMonth ( year , month , amount)
VALUES ( ' 2004 ' , ' Aug ' , 866.0000 )
INSERT INTO sales.salesByMonth ( year , month , amount)
VALUES ( ' 2004 ' , ' Sep ' , 7787.0000 )
INSERT INTO sales.salesByMonth ( year , month , amount)
VALUES ( ' 2004 ' , ' Oct ' , 85576.0000 )
INSERT INTO sales.salesByMonth ( year , month , amount)
VALUES ( ' 2004 ' , ' Nov ' , 855.0000 )
INSERT INTO sales.salesByMonth ( year , month , amount)
VALUES ( ' 2004 ' , ' Dec ' , 5878.0000 )
INSERT INTO sales.salesByMonth ( year , month , amount)
VALUES ( ' 2005 ' , ' Jan ' , 7.0000 )
INSERT INTO sales.salesByMonth ( year , month , amount)
VALUES ( ' 2005 ' , ' Feb ' , 6868.0000 )
INSERT INTO sales.salesByMonth ( year , month , amount)
VALUES ( ' 2005 ' , ' Mar ' , 688.0000 )
INSERT INTO sales.salesByMonth ( year , month , amount)
VALUES ( ' 2005 ' , ' Apr ' , 9897.0000 )
(
year char ( 4 ),
month char ( 3 ),
amount money ,
PRIMARY KEY ( year , month )
)
INSERT INTO sales.salesByMonth ( year , month , amount)
VALUES ( ' 2004 ' , ' Jan ' , 789.0000 )
INSERT INTO sales.salesByMonth ( year , month , amount)
VALUES ( ' 2004 ' , ' Feb ' , 389.0000 )
INSERT INTO sales.salesByMonth ( year , month , amount)
VALUES ( ' 2004 ' , ' Mar ' , 8867.0000 )
INSERT INTO sales.salesByMonth ( year , month , amount)
VALUES ( ' 2004 ' , ' Apr ' , 778.0000 )
INSERT INTO sales.salesByMonth ( year , month , amount)
VALUES ( ' 2004 ' , ' May ' , 78.0000 )
INSERT INTO sales.salesByMonth ( year , month , amount)
VALUES ( ' 2004 ' , ' Jun ' , 9.0000 )
INSERT INTO sales.salesByMonth ( year , month , amount)
VALUES ( ' 2004 ' , ' Jul ' , 987.0000 )
INSERT INTO sales.salesByMonth ( year , month , amount)
VALUES ( ' 2004 ' , ' Aug ' , 866.0000 )
INSERT INTO sales.salesByMonth ( year , month , amount)
VALUES ( ' 2004 ' , ' Sep ' , 7787.0000 )
INSERT INTO sales.salesByMonth ( year , month , amount)
VALUES ( ' 2004 ' , ' Oct ' , 85576.0000 )
INSERT INTO sales.salesByMonth ( year , month , amount)
VALUES ( ' 2004 ' , ' Nov ' , 855.0000 )
INSERT INTO sales.salesByMonth ( year , month , amount)
VALUES ( ' 2004 ' , ' Dec ' , 5878.0000 )
INSERT INTO sales.salesByMonth ( year , month , amount)
VALUES ( ' 2005 ' , ' Jan ' , 7.0000 )
INSERT INTO sales.salesByMonth ( year , month , amount)
VALUES ( ' 2005 ' , ' Feb ' , 6868.0000 )
INSERT INTO sales.salesByMonth ( year , month , amount)
VALUES ( ' 2005 ' , ' Mar ' , 688.0000 )
INSERT INTO sales.salesByMonth ( year , month , amount)
VALUES ( ' 2005 ' , ' Apr ' , 9897.0000 )
我们想要得到类似这样的结果:
Year Jan Feb Mar ..............
----- ---------- ----------- -----------
2004 789.0000 389.0000 8867.0000 .............
2005 7.0000 6868.0000 688.0000 ..............
用 上一篇文章介绍的方法当然可以实现,但现在这里想要的列是固定的,不是动态的,就是12个月,所以也可以这样子来用:
SELECT
year
,
SUM ( case when month = ' Jan ' then amount else 0 end ) AS ' Jan ' ,
SUM ( case when month = ' Feb ' then amount else 0 end ) AS ' Feb ' ,
SUM ( case when month = ' Mar ' then amount else 0 end ) AS ' Mar ' ,
SUM ( case when month = ' Apr ' then amount else 0 end ) AS ' Apr ' ,
SUM ( case when month = ' May ' then amount else 0 end ) AS ' May ' ,
SUM ( case when month = ' Jun ' then amount else 0 end ) AS ' Jun ' ,
SUM ( case when month = ' Jul ' then amount else 0 end ) AS ' Jul ' ,
SUM ( case when month = ' Aug ' then amount else 0 end ) AS ' Aug ' ,
SUM ( case when month = ' Sep ' then amount else 0 end ) AS ' Sep ' ,
SUM ( case when month = ' Oct ' then amount else 0 end ) AS ' Oct ' ,
SUM ( case when month = ' Nov ' then amount else 0 end ) AS ' Nov ' ,
SUM ( case when month = ' Dec ' then amount else 0 end ) AS ' Dec '
FROM sales.salesByMonth
GROUP by year
SUM ( case when month = ' Jan ' then amount else 0 end ) AS ' Jan ' ,
SUM ( case when month = ' Feb ' then amount else 0 end ) AS ' Feb ' ,
SUM ( case when month = ' Mar ' then amount else 0 end ) AS ' Mar ' ,
SUM ( case when month = ' Apr ' then amount else 0 end ) AS ' Apr ' ,
SUM ( case when month = ' May ' then amount else 0 end ) AS ' May ' ,
SUM ( case when month = ' Jun ' then amount else 0 end ) AS ' Jun ' ,
SUM ( case when month = ' Jul ' then amount else 0 end ) AS ' Jul ' ,
SUM ( case when month = ' Aug ' then amount else 0 end ) AS ' Aug ' ,
SUM ( case when month = ' Sep ' then amount else 0 end ) AS ' Sep ' ,
SUM ( case when month = ' Oct ' then amount else 0 end ) AS ' Oct ' ,
SUM ( case when month = ' Nov ' then amount else 0 end ) AS ' Nov ' ,
SUM ( case when month = ' Dec ' then amount else 0 end ) AS ' Dec '
FROM sales.salesByMonth
GROUP by year
但这样事实上还是相当麻烦的,现在SQLSERVER2005中有更方便的实现方法。
SELECT
Year
,
[
Jan
]
,
[
Feb
]
,
[
Mar
]
,
[
Apr
]
,
[
May
]
,
[
Jun
]
,
[
Jul
]
,
[
Aug
]
,
[
Sep
]
,
[
Oct
]
,
[
Nov
]
,
[
Dec
]
FROM (
SELECT year , amount, month
FROM sales.salesByMonth ) AS salesByMonth
PIVOT ( SUM (amount) FOR month IN
( [ Jan ] , [ Feb ] , [ Mar ] , [ Apr ] , [ May ] , [ Jun ] , [ Jul ] , [ Aug ] , [ Sep ] , [ Oct ] , [ Nov ] , [ Dec ] )
) AS ourPivot
ORDER BY Year
FROM (
SELECT year , amount, month
FROM sales.salesByMonth ) AS salesByMonth
PIVOT ( SUM (amount) FOR month IN
( [ Jan ] , [ Feb ] , [ Mar ] , [ Apr ] , [ May ] , [ Jun ] , [ Jul ] , [ Aug ] , [ Sep ] , [ Oct ] , [ Nov ] , [ Dec ] )
) AS ourPivot
ORDER BY Year
就是这样,很简单的用法,效果是完全一样的。
我们再尝试一下把year去掉:
SELECT
[
Jan
]
,
[
Feb
]
,
[
Mar
]
,
[
Apr
]
,
[
May
]
,
[
Jun
]
,
[
Jul
]
,
[
Aug
]
,
[
Sep
]
,
[
Oct
]
,
[
Nov
]
,
[
Dec
]
FROM ( SELECT amount, month
FROM sales.salesByMonth ) AS salesByMonth
PIVOT ( SUM (amount) FOR month IN
( [ Jan ] , [ Feb ] , [ Mar ] , [ Apr ] , [ May ] , [ Jun ] , [ Jul ] , [ Aug ] , [ Sep ] , [ Oct ] , [ Nov ] , [ Dec ] )
) AS ourPivot
FROM ( SELECT amount, month
FROM sales.salesByMonth ) AS salesByMonth
PIVOT ( SUM (amount) FOR month IN
( [ Jan ] , [ Feb ] , [ Mar ] , [ Apr ] , [ May ] , [ Jun ] , [ Jul ] , [ Aug ] , [ Sep ] , [ Oct ] , [ Nov ] , [ Dec ] )
) AS ourPivot
得到的结果是:
Jan Feb Mar ...
---------- ------------ -----------
796.0000 7257.0000 9555.0000 ...
同一个月份的数据累加到一起。
再给个微软官方的例子: