在企业信息化管理中,经常需要提供交叉表形式的数据给管理层。
如以月份为纵轴,客户销售金额为横轴。
以下以SQL自带数据库举例列出SQL语句:
--定义长度为8000的字符串变量@msql
declare @msql varchar(8000)
--横轴字段
select @msql=' select month(orderdate) as 月份'
--从订单表中取出一定条件(where orderdate between '1996-07-01' and '1996-07-31')范围内
--所有客户ID并连接至@msql
select @msql=@msql+',Sum(CASE WHEN Customerid='''+rtrim(customerid)+''' then Freight else 0 end) as ['+rtrim(customerid)+']'
from (select distinct Customerid from orders where orderdate between '1996-07-01' and '1996-07-31')A
如以月份为纵轴,客户销售金额为横轴。
以下以SQL自带数据库举例列出SQL语句:
--定义长度为8000的字符串变量@msql
declare @msql varchar(8000)
--横轴字段
select @msql=' select month(orderdate) as 月份'
--从订单表中取出一定条件(where orderdate between '1996-07-01' and '1996-07-31')范围内
--所有客户ID并连接至@msql
select @msql=@msql+',Sum(CASE WHEN Customerid='''+rtrim(customerid)+''' then Freight else 0 end) as ['+rtrim(customerid)+']'
from (select distinct Customerid from orders where orderdate between '1996-07-01' and '1996-07-31')A