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 a
set @sql = @sql + ' from Product group by ProductName'
exec(@sql)
create table #t (ProductName varchar(10) ,Total int, inDate datetime)
insert #t
select 'Product1', '1', '2012-01-01 00:00:00.000' union all
select 'Product2', '2', '2012-01-23 00:00:00.000' union all
select 'Product3' ,'3', '2012-01-14 00:00:00.000' union all
select 'Product1' ,'3' ,'2012-02-24 00:00:00.000' union all
select 'Product2', '2','2012-02-04 00:00:00.000' union all
select 'Product3', '1', '2012-02-29 00:00:00.000' union all
select 'Product1' ,'10', '2012-06-06 00:00:00.000' union all
select 'Product2' ,'10', '2012-06-16 00:00:00.000' union all
select 'Product3', '10', '2012-06-26 00:00:00.000' union all
select 'Product3', '100', '2012-06-16 00:00:00.000'
--下面的句子只能是固定的项目的行转列。
select ProductName 产品名称 ,[1月]=sum(case date when '01' then Total else 0 end), --需要的继续加..
[2月]=sum(case date when '02' then Total else 0 end),
[6月]=sum(case date when '06' then Total else 0 end),
sum(Total) AS 合计
from (select distinct convert(varchar(2),inDate,0) as date,* from #t) t group by ProductName
drop table #t
CREATE TABLE t1
(
pname VARCHAR(10),
total INT,
indate DATETIME
)
INSERT INTO t1
SELECT 'Product1', 1, '2012-01-01 00:00:00.000' UNION ALL
SELECT 'Product2', 2, '2012-01-23 00:00:00.000' UNION ALL
SELECT 'Product3', 3, '2012-01-14 00:00:00.000' UNION ALL
SELECT 'Product1', 3, '2012-02-24 00:00:00.000' UNION ALL
SELECT 'Product2', 2, '2012-02-04 00:00:00.000' UNION ALL
SELECT 'Product3', 1, '2012-02-29 00:00:00.000' UNION ALL
SELECT 'Product1', 10, '2012-06-06 00:00:00.000' UNION ALL
SELECT 'Product2', 10, '2012-06-16 00:00:00.000' UNION ALL
SELECT 'Product3', 10, '2012-06-26 00:00:00.000' UNION ALL
SELECT 'Product3', 100, '2012-06-16 00:00:00.000'
SELECT * FROM t1
DECLARE @str VARCHAR(8000)
SET @str='select pname'
SELECT @str=@str+',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 a1
SET @str=@str+' from t1 group by pname'
PRINT @str
EXEC (@str)
------------------
pname 1月 2月 6月
Product1 1 3 10
Product2 2 2 10
Product3 3 1 100
