SQL 列转行问题

表内容如下：
施工日期 业务类别 合同号
2009-1-1 A HI001
2010-12-5 B HI002
2008-10-6 C HI003
2011-5-6 A HI001
2012-3-27 F HI004

想统计的结果如下：
年度 业务类别 1月合同数 2月合同数 3月合同数 …
2008 A
2008 B
...
2009
2010
…

每个合同号可能有多次施工记录，比如，合同号HI001，分别在2009-1-1 和2011-5-6施工过，重复合同号不用统计

 

-> 测试数据：[tbl]if object_id('[tbl]') is not null drop table [tbl]create table [tbl]([施工日期] datetime,[业务类别] varchar(1),[合同号] varchar(5))insert [tbl]select '2009-1-1','A','HI001' union allselect '2010-12-5','B','HI002' union allselect '2008-10-6','C','HI003' union allselect '2011-5-6','A','HI001' union allselect '2012-3-27','F','HI004'declare @str varchar(max)set @str=''select @str=@str+','+'['+LTRIM(MONTH([施工日期]))+'月份合同数]'+'=sum(case when MONTH([施工日期])='+ltrim(MONTH([施工日期]))+' then 1 else 0 end)' from tbl group by MONTH([施工日期])print @strexec('select year([施工日期]) as 年份,[业务类别]'+@str+' from tbl group by year([施工日期]),[业务类别]')/*年份 业务类别 1月份合同数 3月份合同数 5月份合同数 10月份合同数 12月份合同数2009 A 1 0 0 0 02011 A 0 0 1 0 02010 B 0 0 0 0 12008 C 0 0 0 1 02012 F 0 1 0 0 0*/

 

方法2

CREATE TABLE TEMP(施工日期 DATETIME,业务类别 VARCHAR(200),合同号 VARCHAR(200))CREATE TABLE TEMP1(施工日期 DATETIME,业务类别 VARCHAR(200),合同号 VARCHAR(200))INSERT INTO TEMPselect '2009-1-1','A','HI001'UNION ALLselect '2010-12-5','B','HI002'UNION ALLselect '2008-10-6','C','HI003'UNION ALLselect '2011-5-6','A','HI001'UNION ALLselect '2012-3-27','F','HI004'DECLARE @施工日期 DATETIME,@业务类别 VARCHAR(200),@合同号 VARCHAR(200)DECLARE CUR CURSOR FORSELECT * FROM TEMPORDER BY 施工日期OPEN CUR FETCH NEXT FROM CUR INTO @施工日期,@业务类别,@合同号WHILE @@FETCH_STATUS=0BEGIN INSERT INTO TEMP1 SELECT @施工日期,@业务类别,@合同号 WHERE NOT EXISTS(SELECT 1 FROM TEMP1 WHERE 合同号=@合同号) FETCH NEXT FROM CUR INTO @施工日期,@业务类别,@合同号ENDCLOSE CURDEALLOCATE CURSELECT YEAR(施工日期) AS [年度] ,业务类别 AS [业务类别], SUM(CASE MONTH(施工日期) WHEN 1 THEN 1 ELSE 0 END) AS [1月合同数], SUM(CASE MONTH(施工日期) WHEN 2 THEN 1 ELSE 0 END) AS [2月合同数], SUM(CASE MONTH(施工日期) WHEN 3 THEN 1 ELSE 0 END) AS [3月合同数], SUM(CASE MONTH(施工日期) WHEN 4 THEN 1 ELSE 0 END) AS [4月合同数], SUM(CASE MONTH(施工日期) WHEN 5 THEN 1 ELSE 0 END) AS [5月合同数], SUM(CASE MONTH(施工日期) WHEN 6 THEN 1 ELSE 0 END) AS [6月合同数], SUM(CASE MONTH(施工日期) WHEN 7 THEN 1 ELSE 0 END) AS [7月合同数], SUM(CASE MONTH(施工日期) WHEN 8 THEN 1 ELSE 0 END) AS [8月合同数], SUM(CASE MONTH(施工日期) WHEN 9 THEN 1 ELSE 0 END) AS [9月合同数], SUM(CASE MONTH(施工日期) WHEN 10 THEN 1 ELSE 0 END) AS [10月合同数], SUM(CASE MONTH(施工日期) WHEN 11 THEN 1 ELSE 0 END) AS [11月合同数], SUM(CASE MONTH(施工日期) WHEN 12 THEN 1 ELSE 0 END) AS [12月合同数] FROM TEMP1GROUP BY YEAR(施工日期),MONTH(施工日期),业务类别ORDER BY YEAR(施工日期),业务类别DROP TABLE TEMPDROP TABLE TEMP1