VB+MS SqlServer，是我们目前开发数据库应用系统最常用的模式，翻翻以前的老帖子，有一些SqlServer的问题经常被提出来，但正确解答甚少，现把我对这些问题的见解贴出来，这次先讲讲动态交叉表的问题
为了说明问题，我们用SqlServer自带的事例数据库(Northwind)来进行验证，所有的例子请放到Northwind中运行，我可能会省略Use语句，所引用的表，都是Northwind中的，下面我就不再说明了
我这里指的交叉表，就是象Access的交叉表查询一样的效果，比如Employees表中City字段代表了城市的名称，TitleOfCourtesy代表称呼，我们希望按照City和TitleOfCourtesy的情况来统计ReportsTo字段的合计数（本统计没有任何实际意义，只是挑选一些记录包含重复内容的字段来说明情况），并显示成以下格式：（TitleOfCourtesy作为行，City作为列）
| TitleOfCourtesy | LondonCity | RedmondCity | SeattleCity |
| Dr. | |||
| Mr. | 12 | ||
| Mrs. | 2 | ||
| Ms. | 5 | 4 |
探讨这个问题之前，我们首先来看一下如何建立静态的交叉表，也就是说列数固定的交叉表，这种情况其实只要一句简单的Select查询就可以搞定：
SELECT TitleOfCou rtesy,
SUM(CASE City WHEN 'London' THEN ReportsTo ELSE NULL END) AS [London City],
SUM(CASE City WHEN 'Redmond' THEN ReportsTo ELSE NULL END) AS [Redmond City],
SUM(CASE City WHEN 'Seattle' THEN ReportsTo ELSE NULL END) AS [Seattle City]
FROM Employees GROUP BY TitleOfCourtesy
其中利用了CASE语句判断，如果是相应的列，则取需要统计的ReportsTo数值，否则取NULL，然后再合计
其中有两个常见问题说明一下：
a、用NULL而不用0是有道理的，假如用0，虽然求和函数SUM可以取到正确的数，但类似COUNT函数（取记录个数），结果就不对了，因为Null不算一条记录，而0要算，同理空字串（""）也是这样，总之在这里应该用NULL,这样任何函数都没问题。
b、假如在视图的设计界面保存以上的查询，则会报错“没有输出列”，从而无法保存，其实只要在查询前面加上一段：Create View ViewName AS ...，ViewName是你准备给查询起的名称，...就是我们的查询，然后运行一下，就可以生成视图了，对于其他一些设计器不支持的语法，也可以这样保存。
以上查询作用也很大，对于很多情况，比如按照季度统计、按照月份统计等列头内容固定的情况，这样就行了，但往往大多数情况下列头内容是不固定的，象City，用户随时可能删除、添加一些城市，这种情况，我们就需要用存储过程来解决：
总体思路其实很简单，首先检索列头信息，形成一个游标，然后遍历游标，将上面查询语句里Case判断的内容用游标里的值替代，形成一条新的Sql查询，然后执行，返回结果，就可以了，以下是我写的一个存储过程，供大家参考：
CREATE procedure CorssTab
@strTabName as varchar(50) = 'Employees', --此处放表名
@strCol as varchar(50) = 'City', --表头分组依据字段
@strGroup as varchar(50) = 'TitleOfCourtesy',--分组字段
@strNumber as varchar(50) = 'ReportsTo', --被统计的字段
@strSum as varchar(10) = 'Sum' --运算方式
AS
DECLARE @strSql as varchar(1000), @strTmpCol as varchar(100)
EXECUTE ('DECLARE corss_cursor CURSOR FOR SELECT DISTINCT ' + @strCol + ' from ' + @strTabName + ' for read only ') --生成游标
begin
SET nocount ON
SET @strsql ='select ' + @strGroup + ', ' + @strSum + '(' + @strNumber + ') AS [' + @strSum + ' of ' + @strNumber + ']' --查询的前半段
OPEN corss_cursor
while (0=0)
BEGIN
FETCH NEXT FROM corss_cursor --遍历游标，将列头信息放入变量@strTmpCol
INTO @strTmpCol
if (@@fetch_status<>0) break
SET @strsql = @strsql + ', ' + @strSum + '(CASE ' + @strCol + ' WHEN ''' + @strTmpCol + ''' THEN ' + @strNumber + ' ELSE Null END) AS [' + @strTmpCol + ' ' + @strCol + ']' --构造查询
END
SET @strsql = @strsql + ' from ' + @strTabname + ' group by ' + @strGroup --查询结尾
EXECUTE(@strsql) --执行
IF @@error <>0 RETURN @@error --如果出错，返回错误代码
CLOSE corss_cursor
DEALLOCATE corss_cursor RETURN 0 --释放游标，返回0表示成功
end
GO
几点说明：
a、这是一个通用存储过程，使用时@strTabName、@strCol、@strGroup、@strNumber、@strSum几个变量设置一下就可以用到其他表上，其中结果集的第二列我加了个合计列
b、为了测试方便，我在存储过程中设置了默认值，就是前面提到的Employees表，这样直接运行时就可以出来我上面提到的结果。
c、使用时，可以把上面的代码复制到企业管理器的查询设计界面Sql窗格，或者查询分析器里运行一下（注意正确选择NorthWind数据库），就可以生成一个存储过程：CorssTab，然后直接运行CorssTab，如果出现本文前面类似的窗格，就表示运行成功了。
d、假如用于其它表，首先需要在你的用户数据库里生成此存储过程（当然也可以放到Master里，然后再加个变量：@DataBase，赋值为数据库名称，然后在上面代码打开指定数据库，这样所有的数据库都可以调用它），当你调用时，采取以下格式：
CorssTab @strTabName = 'Orders', @strCol = 'DATEPART(yy, OrderDate)',@strGroup = 'CustomerID', @strNumber = 'OrderID', @strSum = 'Count'
上面这条语句统计了NorthWind中Orders表里每个客户年度订单数量，大家可以运行试一下效果，虽然列头显示的名称不恰当，但基本效果出来了，相信大家通过对我的代码再作简单修改，可以达到满意的交叉表效果。
下次我再讲讲，如何给查询的记录集自动加行号
动态SQL的使用例子, 行列转换
把列变成行的sql语句
线有如下表：
科目 分数 姓名
语文 88 董兆
数学 95 董兆
英语 89 董兆
语文 69 婵娟
数学 95 婵娟
英语 89 婵娟
语文 69 李慧
数学 95 李慧
英语 89 李慧
一条sql语句，查询结果是
李慧 婵娟 董兆
语文 69 69 88
数学 95 95 95
英语 89 89 89
sql语句如下:
create table k(科目 varchar(50),分数 int,姓名 varchar(50))
insert k select '语文',88,'董兆'
union all select '数学',95,'董兆'
union all select '英语',89,'董兆'
union all select '语文',69,'婵娟'
union all select '数学',95,'婵娟'
union all select '英语',89,'婵娟'
union all select '语文',69,'李慧'
union all select '数学',95,'李慧'
union all select '英语',89,'李慧'
declare @s varchar(8000)
set @s='select 科目'
select @s=@s+',['+姓名+']=sum(case 姓名 when '''+姓名+''' then 分数 else 0 end)'
from k group by 姓名
exec(@s+'from k group by 科目')
应用SQL交叉表实现行列转换
--数据结构
| ID | int | 4 | 0 |
| Grade | varchar | 50 | 1 |
| ExamYear | varchar | 50 | 1 |
| Name | varchar | 50 | 1 |
| Subject | varchar | 50 | 1 |
| Score | int | 4 | 1 |
--数据体
| ID | Grade | ExamYear | Name | Subject | Score |
| 1. | 2004级2班 | 2004 | 李明 | 数学 | 100 |
| 2. | 2004级2班 | 2004 | 王强 | 数学 | 90 |
| 3. | 2004级2班 | 2004 | 付超 | 数学 | 85 |
| 4. | 2004级2班 | 2004 | 李明 | 语文 | 75 |
| 5. | 2004级2班 | 2004 | 王强 | 语文 | 89 |
| 6. | 2004级2班 | 2004 | 付超 | 化学 | 89 |
| 7. | 2004级2班 | 2004 | 李明 | 化学 | 45 |
| 8. | 2004级2班 | 2004 | 王强 | 化学 | 78 |
| 9. | 2004级2班 | 2005 | 李明 | 数学 | 89 |
| 10. | 2004级2班 | 2005 | 王强 | 数学 | 88 |
| 11. | 2004级2班 | 2005 | 付超 | 数学 | 86 |
| 12. | 2004级2班 | 2005 | 李明 | 语文 | 78 |
| 13. | 2004级2班 | 2005 | 王强 | 语文 | 92 |
| 14. | 2004级2班 | 2005 | 付超 | 化学 | 94 |
| 15. | 2004级2班 | 2005 | 付超 | 语文 | 59 |
| 16. | 2004级2班 | 2005 | 李明 | 化学 | 54 |
| 17. | 2004级2班 | 2005 | 王强 | 化学 | 80 |
--SQL语句
Declare@SqlStrnvarchar(2000)
--构建Sql语句，生成2004级2班 2004年的考虑成绩表
Select@SqlStr='selectGrade,ExamYear,Name,'
--生成条件选择语句，使用Distinct把所有科目全部列出，当然，可以加上条件，如：仅某一年度的考试科目
Select@SqlStr=@SqlStr+'SUM(CASESubjectWHEN'''+Subject+'''THENScoreELSE0END)AS'''+Subject+''','from(SelectDistinctSubjectfromExamResultwhereExamYear=2004)AsTemTable
--补全Sql语句，并使用GroupBy对重复的记录进行汇总
Select@SqlStr=left(@SqlStr,Len(@SqlStr)-1)+'fromExamResultwhereExamYear=2004GroupByName,Grade,ExamYear'
--执行Sql
Exec(@SqlStr)
Go
--执行结果
行列转换例子
一、現有一個表TB1，其字段如下：(Name：代表姓名，Province：代表省份，Score：代表業務顧客數量)
Name Province Score
李三 四川 5
小王 四川 3
小張 廣州 3
李三 廣州 2
小張 湖南 3
李三 湖南 4
得到下面的結果：
姓名 四川 廣州 湖南 總計
李三 5 2 4 11
小王 3 0 0 3
小張 0 3 3 6
注意：省份必須根據TB1表中所有出現的省份進行統計
create table A
(
Name varchar(10),
Province varchar(20),
Score int
)
insert A
select '李三','四川',5 union
select '小王','四川',3 union
select '小張','廣州',3 union
select '李三','廣州',2 union
select '小張','湖南',3 union
select '李三','湖南',4
--测试
declare @s varchar(8000)
set @s = ''
select @s = @s +','+Province+'= sum(case province when'''+province+'''then score else 0 end)'
from A group by Province order by Province
exec ('select name '+@s+',sum(Score) As Total from A group by Name order by Name')
--测试结果
-- Name 广州 湖南 四川 Total
-- 1 李三24511
-- 2 小王0033
-- 3 小張3306
--测试结束
drop table A
二、这是邹建写的一个交叉表的实例
--测试数据
CREATE TABLE #XS_REP_CLIENTPROD_CHX
(
C_PTID varchar (16) NULL ,
C_PTNAME varchar(80) NULL,
c_PCID varchar(50) NULL,
C_CUSTID varchar (15) NULL ,
c_CustName varchar (100) NULL ,
-- N_PRICE numeric(18, 2) NULL ,
-- N_PRENOINVOICE numeric(18, 3) NULL ,
N_SALEWEIGHT numeric(18, 3) NULL --,
-- N_INVOICEWEIGHT numeric(18, 3) NULL ,
-- N_NOINVOICE numeric(18, 3) NULL,
-- N_PRENOINVOICE_m numeric(18, 3) NULL,
-- N_SALEWEIGHT_m numeric(18, 3) NULL ,
-- N_INVOICEWEIGHT_m numeric(18, 3) NULL ,
-- N_NOINVOICE_m numeric(18, 3) NULL
)
insert #XS_REP_CLIENTPROD_CHX
select '001001','葡萄','0201','0201001','客户1',1
union all select '001001','葡萄','0201','0201002','客户2',2
union all select '001001','苹果','0201','0201001','客户1',3
union all select '001001','苹果','0201','0201002','客户2',4
go
--查询
declare @s varchar(8000)
set @s=''
select @s=@s+',['+C_PTNAME+']=sum(case C_PTNAME when '''
+C_PTNAME+''' then N_SALEWEIGHT else 0 end)'
from #XS_REP_CLIENTPROD_CHX
group by C_PTNAME
exec('
select c_PCID=case
when grouping(c_PCID)=1 then ''总计''
else c_PCID end
,C_CUSTID=case
when grouping(c_PCID)=1 then ''''
when grouping(C_CUSTID)=1 then ''小计''
else C_CUSTID end
,c_CustName=case
when grouping(c_CustName)=0 then c_CustName
else '''' end
'+@s+'
from #XS_REP_CLIENTPROD_CHX
group by c_PCID,C_CUSTID,c_CustName with rollup
having (grouping(c_PCID)=1 or grouping(C_CUSTID)=1)
or grouping(c_CustName)=0
')
go
--删除测试
drop table #XS_REP_CLIENTPROD_CHX
/*--测试结果
c_PCID C_CUSTID c_CustName 苹果 葡萄
---------- --------------- -------------------- ----------- ---------
0201 0201001 客户1 3.000 1.000
0201 0201002 客户2 4.000 2.000
0201 小计 7.000 3.000
总计 7.000 3.000
--*/
三 行列数
create table fbill(fauxqty int,Fdeptidname varchar(20),fsupplyidname varchar(20))
insert into fbill select 50,'信息部','十里庙 '
insert into fbill select 20,'业务部','经济开发'
insert into fbill select 20,'销售部','十里庙 '
insert into fbill select 1 ,'信息部','十里庙 '
insert into fbill select 2 ,'信息部','经济开发'
insert into fbill select 30,'业务部','经济开发'
insert into fbill select 5 ,'业务部','经济开发'
insert into fbill select 6 ,'业务部','经济开发'
go
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+','+Fdeptidname+'=sum(case Fdeptidname when '''+Fdeptidname+''' then fauxqty else 0 end)'
from fbill group by Fdeptidname
set @sql='select fsupplyidname as 客户'+@sql+' from fbill group by fsupplyidname'
exec(@sql)
go
/*
客户 销售部 信息部 业务部
-------------------- ----------- ----------- -----------
经济开发 0 2 61
十里庙 20 51 0
*/
drop table fbill
go
经常用到的交叉表问题,一般用动态SQL能生成动态列！
--原贴
http://community.csdn.net/Expert/topic/4200/4200386.xml?temp=.4856989
原始表如下格式：
Class CallDate CallCount
1 2005-8-8 40
1 2005-8-7 6
2 2005-8-8 77
3 2005-8-9 33
3 2005-8-8 9
3 2005-8-7 21
根据Class的值，按日期分别统计出CallCount1，CallCount2，CallCount3。
当该日期无记录时值为0
要求合并成如下格式：
CallDate CallCount1 CallCount2 CallCount3
2005-8-9 0 0 33
2005-8-8 40 77 9
2005-8-7 6 0 21
--创建测试环境
Create table T (Class varchar(2),CallDate datetime, CallCount int)
insert into T select '1','2005-8-8',40
union all select '1','2005-8-7',6
union all select '2','2005-8-8',77
union all select '3','2005-8-9',33
union all select '3','2005-8-8',9
union all select '3','2005-8-7',21
--动态SQL
declare @s varchar(8000)
set @s='select CallDate '
select @s=@s+',[CallCount'+Class+']=sum(case when Class='''+Class+''' then CallCount else 0 end)'
from T
group by Class
set @s=@s+' from T group by CallDate order by CallDate desc '
exec(@s)
--结果
CallDate CallCount1 CallCount2 CallCount3
------------------------------------------------------ ----------- ----------- -----------
2005-08-09 00:00:00.000 0 0 33
2005-08-08 00:00:00.000 40 77 9
2005-08-07 00:00:00.000 6 0 21
--删除测试环境
drop table T
详细介绍SQL交叉表的实例
很简单的一个东西，见网上好多朋友问“怎么实现交叉表?”，以下是我写的一个例子，数据库基于SQL SERVER 2000。
交叉表实例
建表:
在查询分析器里运行:
CREATE TABLE [Test] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[subject] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Source] [numeric](18, 0) NULL
) ON [PRIMARY]
GO
INSERT INTO [test] ([name],[subject],[Source]) values (N'张三',N'语文',60)
INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'数学',70)
INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'英语',80)
INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'数学',75)
INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'语文',57)
INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'语文',80)
INSERT INTO [test] ([name],[subject],[Source]) values (N'张三',N'英语',100)
Go
交叉表语句的实现:
用于:交叉表的列数是确定的
select name,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 test
group by name
--用于:交叉表的列数是不确定的
declare @sql varchar(8000)
set @sql = 'select name,'
select @sql = @sql + 'sum(case subject when '''+subject+'''
then source else 0 end) as '''+subject+''','
from (select distinct subject from test) as a
select @sql = left(@sql,len(@sql)-1) + ' from test group by name'
exec(@sql)
go
运行结果:
将日期转换为字符串作为列以及含有行列合计的交叉表：
演示代码如下：
if exists (select 1 from sysobjects where id = object_id('test') and type ='U')
drop table test
go
--建立测试环境
set nocount on
--drop table test
create table test(model varchar(20),date datetime ,qty int)
insert into test select '8A09208G','2009-5-8 12:23:24.550','100'
insert into test select '8A09208G','2009/5/10 10:18:46.480','500'
insert into test select '8B02806G','2009/5/8 13:24:16.123','1000'
insert into test select '8B02806G','2009/5/9 15:26:37.123','2000'
insert into test select '8B02806G','2009/5/10 03:08:56.123','1000'
insert into test select '8C00184G','2009/5/10 08:09:43.123','2000'
insert into test select '8D18302G','2009/5/10 18:26:37.123','3000'
insert into test select '8E04305G','2009/5/11 20:19:21.123','2500'
insert into test select '8E04305G','2009/5/12 16:24:54.123','1000'
insert into test select '8F00203G','2009/5/12 21:53:45.123','1500'
go
--测试
--日期范围选择
declare @startdate datetime --查询的起始日期
declare @enddate datetime --查询的最后日期
set @startdate =cast('2009/5/8 15:13:12' as datetime)
set @enddate = cast('2009/5/10' as datetime)
--去掉时间部分的影响，保留日期部分，时间变为00：00：00.000，
--这样同一天不同时间的值变成相同的了
set @startdate=cast(convert(char(10),@startdate,120) as datetime)
set @enddate=cast(convert(char(10),@enddate,120) as datetime)
--select @startdate
--生成动态列（日期数据转换为要求的字符格式）
declare @sql varchar(8000)
set @sql= 'select model =CASE WHEN GROUPING(model)=0 THEN model ELSE''总计'' END'
--注意字符值不能用双引号，此处用了两个单引号''总计''
--产生动态日期列
declare @sql01 varchar(8000)
set @sql01=''
select @sql01=@sql01+', ['+strdate+']= sum(case when
convert(char(6),date,12)='+strdate+ ' then qty else 0 end)'
from (select distinct top 100 percent convert(char(6),date,12) as strdate
from test where (cast(convert(char(10),date,120) as datetime) >= @startdate
and cast(convert(char(10),date,120) as datetime) <= @enddate) order by
convert(char(6),date,12)) as selecteddate
--产生最终的查询语句
select @sql=@sql+@sql01
--加上model的合计数及查询有关的表的相关部分
set @sql=@sql +', 合计=sum(qty) from test where
((convert(char(8),date,112)>='
set @sql=@sql+convert(char(8),@startdate, 112)+') and
(convert(char(8),date,112)<= '
set @sql=@sql+convert(char(8),@enddate, 112)+')) group by model with rollup'
--select @sql ---输出查询语句
--执行查询
exec( @sql)
/*输出结果
model 090508 090509 090510 合计
-------------------- ----------- ----------- ----------- -----------
8A09208G 100 0 500 600
8B02806G 1000 2000 1000 4000
8C00184G 0 0 2000 2000
8D18302G 0 0 3000 3000
总计 1100 2000 6500 9600
*/
--删除测试环境
drop table test
set nocount off
-------------------------------全文结束------------------------------------------
联系客服
