SQL以前学过一点点基础的，稍微复杂一点的就不知道了。我搞了几个小时还是没有搞定，郁闷ing，希望各位童鞋指点。
有一张表Product数据有
ProductName Total inDate
Product1 1 2012-01-01 00:00:00.000
Product2 2 2012-01-23 00:00:00.000
Product3 3 2012-01-14 00:00:00.000
Product1 3 2012-02-24 00:00:00.000
Product2 2 2012-02-04 00:00:00.000
Product3 1 2012-02-29 00:00:00.000
Product1 10 2012-06-06 00:00:00.000
Product2 10 2012-06-16 00:00:00.000
Product3 10 2012-06-26 00:00:00.000
Product3 100 2012-06-16 00:00:00.000
要求得出的结果是：
产品名称 1月 2月 6月
product1 1 3 10
product2 2 2 10
product3 3 1 110
我写的SQL语句如下：
declare @sql varchar(8000)
set @sql = 'select ProductName 产品名称'
select @sql = @sql + ' , max(case inDate when ''' + CONVERT(varchar(100), inDate, 102) + ''' then Total else 0 end) '
from (select distinct inDate from Product) as a
set @sql = @sql + ' from (select ProductName,sum(Total) as Total,inDate from Product group by inDate,ProductName) as tb group by ProductName'
exec(@sql)
但是结果是安日统计的，不是按照要求按月统计的，我做过改动，始终达不到按月统计的报表。。。
------解决方案--------------------
- SQL code
declare @sql varchar(8000)set @sql = 'select ProductName 产品名称'select @sql = @sql + ' ,max(case CONVERT(varchar(7),inDate,120) when ''' +dm+ ''' then Total else 0 end) as ['+dm+']'from (select distinct CONVERT(varchar(7),inDate,120) as dm from Product) as aset @sql = @sql + ' from Product group by ProductName'exec(@sql)
------解决方案--------------------
- SQL code
CREATE TABLE t1( pname VARCHAR(10), total INT, indate DATETIME)INSERT INTO t1SELECT 'Product1', 1, '2012-01-01 00:00:00.000' UNION ALLSELECT 'Product2', 2, '2012-01-23 00:00:00.000' UNION ALLSELECT 'Product3', 3, '2012-01-14 00:00:00.000' UNION ALLSELECT 'Product1', 3, '2012-02-24 00:00:00.000' UNION ALLSELECT 'Product2', 2, '2012-02-04 00:00:00.000' UNION ALLSELECT 'Product3', 1, '2012-02-29 00:00:00.000' UNION ALLSELECT 'Product1', 10, '2012-06-06 00:00:00.000' UNION ALLSELECT 'Product2', 10, '2012-06-16 00:00:00.000' UNION ALLSELECT 'Product3', 10, '2012-06-26 00:00:00.000' UNION ALLSELECT 'Product3', 100, '2012-06-16 00:00:00.000'SELECT * FROM t1DECLARE @str VARCHAR(8000)SET @str='select pname'SELECT @[email protected]+',max(case when month(indate)='+LTRIM(indate)+' then total else null end) as ['+LTRIM(indate)+'月'+']'FROM (SELECT DISTINCT MONTH(indate) AS indate FROM t1) AS a1SET @[email protected]+' from t1 group by pname'PRINT @strEXEC (@str)------------------pname 1月 2月 6月Product1 1 3 10Product2 2 2 10Product3 3 1 100