SqlServer生成交叉表大全
我的图书馆
SqlServer生成交叉表大全
SqlServer如何生成动态交叉表查询 VB+MS SqlServer，是我们目前开发数据库应用系统最常用的模式，翻翻以前的老帖子，有一些SqlServer的问题经常被提出来，但正确解答甚少，现把我对这些问题的见解贴出来，这次先讲讲动态交叉表的问题 为了说明问题，我们用SqlServer自带的事例数据库(Northwind)来进行验证，所有的例子请放到Northwind中运行，我可能会省略Use语句，所引用的表，都是Northwind中的，下面我就不再说明了 我这里指的交叉表，就是象Access的交叉表查询一样的效果，比如Employees表中City字段代表了城市的名称，TitleOfCourtesy代表称呼，我们希望按照City和TitleOfCourtesy的情况来统计ReportsTo字段的合计数（本统计没有任何实际意义，只是挑选一些记录包含重复内容的字段来说明情况），并显示成以下格式：（TitleOfCourtesy作为行，City作为列）
TitleOfCourtesy LondonCity RedmondCity SeattleCity
Mr. 12 & &
Mrs. & 2 &
探讨这个问题之前，我们首先来看一下如何建立静态的交叉表，也就是说列数固定的交叉表，这种情况其实只要一句简单的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表示成功 endGO 几点说明：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的使用例子, 行列转换
drop&table&#testcreate&table&#test(name&&nvarchar(20),type&&nvarchar(20),category&&nvarchar(20))select&*&from&#testinsert&into&#test(name,type,category)&values&('n1','t1','c1');insert&into&#test(name,type,category)&values&('n2','t1','c2');insert&into&#test(name,type,category)&values&('n3','t2','c1');insert&into&#test(name,type,category)&values&('n4','t3','c3');insert&into&#test(name,type,category)&values&('n5','t2','c4');insert&into&#test(name,type,category)&values&('n6','t3','c5');insert&into&#test(name,type,category)&values&('n1','t1','c1');--select&category,name,sum(case&when&type='t1'&then&1&else&0&end),sum(case&when&type='t2'&then&1&else&0&end),sum(case&when&type='t3'&then&1&else&0&end)&from&#test&group&by&name,category--如果type不固定--使用動態SQL語句&Declare&@S&Varchar(8000)Select&@S&=&'Select&&&&&category,&name'Select&@S&=&@S&+&',&SUM(Case&type&When&'''&+&type&+&'''&Then&1&Else&0&End)&As&'&+&typeFrom&#TEST&Group&By&typeSelect&@S&=&@S&+&'&From&#TEST&Group&By&category,&name&Order&By&category,&name'print&@SEXEC(@S)GO & &
--测试数据&&&行转列&&Create&&&table&&&test&&&(name&&&char(10),km&&&char(10),cj&&&int)&&&&&insert&&&test&&&values('张三','语文',80)&&&&&insert&&&test&&&values('张三','数学',86)&&&&&insert&&&test&&&values('张三','英语',75)&&&&&insert&&&test&&&values('李四','语文',78)&&&&&insert&&&test&&&values('李四','数学',85)&&&&&insert&&&test&&&values('李四','英语',77)&&&&&&&&&--查询&&&&&declare&&&@sql&&&varchar(8000),@s1&&&varchar(8000)&&&&&select&&&@sql&&&=&&&'',@s1=''&&&&&&&&&select&&&@sql&&&=&&&@sql+&&&',['+km+']=sum(case&&&km&&&when&&&'''+km+'''&&&then&&&cj&&&else&&&0&&&end)'&&&&&,@s1=@s1+',sum(case&&&km&&&when&&&'''+km+'''&&&then&&&cj&&&else&&&0&&&end)/sum(case&&&km&&&when&&&'''+km+'''&&&then&&&1&&&else&&&0&&&end)'&&&&&from&&&test&&&&&&&group&&&by&&&km&&&&&exec('select&&&name=case&&&grouping(name)&&&when&&&1&&&then&&&''全班总分''&&&else&&&name&&&end'+@sql+',小计=sum(cj)&&&&&from&&&test&&&&&group&&&by&&&name&&&with&&&rollup&&&&&union&&&all&&&&&select&&&''全班平均分'''+@s1+',sum(cj)/count(distinct&&&name)&&&&&from&&&test')&&&&&go&&&&&&&&&--删除测试&&&&&drop&&&table&&&test&&& &
--MS&SQL2000下月份不固定的動態寫法Create&Table&TEST(class&&&&Nvarchar(10),&name&&&&Nvarchar(10),&年份&&&&Int,&[1月]&&&&&&&&Varchar(10),&[2月]&&&&&&&&Varchar(10),&[3月]&&&&&&&&Varchar(10))Insert&TEST&Select&N'一班',N'张三',2007,'5元','2元','5元'Union&All&Select&N'一班',N'李四',2006,'3元','0元','1元'Union&All&Select&N'二班',N'王五',2007,'0元','0元','1元'GODeclare&@S&Nvarchar(4000)Select&@S&=&''Select&@S&=&@S&+&'&Union&Select&class,&name,&年份,&'''&+&Name&+&'''&As&月份,&['&+&Name&+&']&As&元&&From&TEST&'&From&SysColumns&Where&ID&=&OBJECT_ID('TEST')&And&Name&Like&'%月'&Order&By&NameSelect&@S&=&Stuff(@S,&1,&7,&'')Print&@SEXEC(@S)GODrop&Table&TEST &
--动态月份2005&处理如下:--测试环境create&table&tb_tb(class&varchar(10),name&varchar(10),年份&varchar(10),[1月]&varchar(10),[2月]&varchar(10),[3月]&varchar(10))insert&into&tb_tb&select&'一班','张三','2007','5元','2元','5元'union&all&select&'一班','李四','2006','3元','0元','1元'union&all&select&'二班','王五','2007','0元','0元','1元'--计算月份:declare&@月份&varchar(100)set&@月份='';select&@月份=@月份+',['+name+']'&from&sys.columns&where&object_id=object_id('tb_tb')and&name&like&'%月'set&&@月份=stuff(@月份,1,1,'')--交叉表处理exec('select&*&from&tb_tbunpivot&&&&(&金额&for&月份&in&('+@月份+'))&unptwhere&金额&&''0元''')--删除测试环境drop&table&tb_tb & &
--建立測試環境Create&Table&表1([id]&&&&Int,&[名称]&&&&Nvarchar(20))Insert&表1&Select&1,&&&&&&&N'名称1'Union&All&Select&2,&&&&&&&N'名称2'Union&All&Select&3,&&&&&&&N'名称3'Create&Table&表2([id]&&&&&&&&Int,&[时间]&&&&Nvarchar(10),&[地点]&&&&Nvarchar(10))Insert&表2&Select&1,&&&&&&&&&&N'5日',&&&&&&&&N'上海'Union&All&Select&1,&&&&&&&&&&N'9日',&&&&&&&&N'北京'Union&All&Select&1,&&&&&&&&&&N'20日',&&&&&&N'天津'Union&All&Select&2,&&&&&&&&&&N'8日',&&&&&&&&N'杭州'Union&All&Select&2,&&&&&&&&&&N'19日',&&&&&&&N'广州'Union&All&Select&3,&&&&&&&&&&N'8日',&&&&&&&&N'深圳'GO--創建函數Create&Function&F_TEST(@id&Int)ReturnS&Nvarchar(4000)AsBegin&&&&Declare&@S&Nvarchar(4000)&&&&Select&@S&=&''&&&&Select&@S&=&@S&+&';'&+&时间&+&'-'&+&地点&From&表2&Where&id&=&@id&&&&Select&@S&=&Stuff(@S,&1,&1,&'')&&&&Return&@SEndGO--測試Select&&&&id,&&&&dbo.F_TEST(id)&As&[时间、地点]From&&&&表1GO--刪除測試環境Drop&Table&表1,&表2Drop&Function&F_TEST
& 把列变成行的sql语句 线有如下表： & 科目&& 分数&&& 姓名& 语文&&&& 88&&&&& 董兆& 数学&&& 95&&&&&& 董兆& 英语&&&& 89&&&&& 董兆 & 语文&&&& 69&&&&& 婵娟& 数学&&& 95&&&&&& 婵娟& 英语&&&& 89&&&&& 婵娟 & 语文&&&& 69&&&&& 李慧& 数学&&& 95&&&&&& 李慧& 英语&&&& 89&&&&& 李慧 一条sql语句，查询结果是 &&&&&&&& 李慧 婵娟 董兆& 语文&& 69&&& 69&& 88& 数学&& 95&&&& 95& 95& 英语&& 89&&& 89&& 89sql语句如下: 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交叉表实现行列转换
--数据结构 &
1 --数据体 &
1.&&&&&&&& &
2.&&&&&&&& &
3.&&&&&&&& &
4.&&&&&&&& &
5.&&&&&&&& &
6.&&&&&&&& &
7.&&&&&&&& &
8.&&&&&&&& &
9.&&&&&&&& &
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 & --执行结果 & & 1 2004级2班 2004 付超 89 85 56 2 2004级2班 2004 李明 45 100 75 3 2004级2班 2004 王强 78 90 89
行列转换例子 一、現有一個表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 Aselect '李三','四川',5 unionselect '小王','四川',3 unionselect '小張','廣州',3 unionselect '李三','廣州',2 unionselect '小張','湖南',3 unionselect '李三','湖南',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 ) 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',1union all select '001001','葡萄','0201','0201002','客户2',2 union all select '001001','苹果','0201','0201001','客户1',3union all select '001001','苹果','0201','0201002','客户2',4go --查询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_CHXgroup by C_PTNAMEexec('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&from #XS_REP_CLIENTPROD_CHXgroup by c_PCID,C_CUSTID,c_CustName with rolluphaving (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.0000201&&&&&& 0201002&&&&&&&& 客户2&&&&&&&&&&&&&&&&& 4.000&&&&& 2.0000201&&&&&& 小计&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 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 客户 from fbill group by fsupplyidname'exec(@sql)go /*客户&&&&&&&&&&&&&&&&&& 销售部&&&&&&&& 信息部&&&&&&&& 业务部&&&&&&&& -------------------- ----------- ----------- ----------- 经济开发&&&&&&&&&&&&&&&& 0&&&&&&&&&& 2&&&&&&&&&& 61十里庙&&&&&&&&&&&&&&&&& 20&&&&&&&&& 51&&&&&&&&& 0*/ drop table fbillgo
经常用到的交叉表问题,一般用动态SQL能生成动态列！
原始表如下格式：Class&&&& CallDate&&& CallCount1　　　　 &&& 401　　　　 &&& 62　　　　 &&& 773　　　　 &&& 333　　　　 &&& 93　　　　 &&& 21 根据Class的值，按日期分别统计出CallCount1，CallCount2，CallCount3。当该日期无记录时值为0要求合并成如下格式：CallDate　　CallCount1　　CallCount2　　CallCount3　　0 　　　　　　0　　　　　　 33　　40　　　　　　77　　　　　　9　　6　　　　　　 0　　　　　　 21 --创建测试环境Create table& T& (Class varchar(2),CallDate datetime, CallCount int)insert into T select '1','',40union all select '1','',6union all select '2','',77union all select '3','',33union all select '3','',9union all select '3','',21--动态SQLdeclare @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 Classset @s=@s+' from T group by CallDate order by CallDate desc 'exec(@s) --结果 CallDate&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& CallCount1& CallCount2& CallCount3& ------------------------------------------------------ ----------- ----------- -----------
00:00:00.000&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 0&&&&&&&&&& 0&&&&&&&&&& 33 00:00:00.000&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 40&&&&&&&&& 77&&&&&&&&& 9 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',' 12:23:24.550','100' insert into test select '8A09208G',' 10:18:46.480','500' insert into test select '8B02806G',' 13:24:16.123','1000' insert into test select '8B02806G',' 15:26:37.123','2000' insert into test select '8B02806G',' 03:08:56.123','1000' insert into test select '8C00184G',' 08:09:43.123','2000' insert into test select '8D18302G',' 18:26:37.123','3000' insert into test select '8E04305G',' 20:19:21.123','2500' insert into test select '8E04305G',' 16:24:54.123','1000' insert into test select '8F00203G',' 21:53:45.123','1500' go --测试 --日期范围选择 declare @startdate datetime --查询的起始日期 declare @enddate datetime --查询的最后日期 set @startdate =cast(' 15:13:12' as datetime) set @enddate = cast('' 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 & -------------------------------全文结束------------------------------------------
TA的推荐TA的最新馆藏[转]&
喜欢该文的人也喜欢