SELECT
tClassID,
JAN = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=1),
FRI = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=2),
MAR = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=3),
APR = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=4),
MAY = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=5),
JUE = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=6),
JUL = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=7),
AGU = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=8),
SEP = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=9),
OCT = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=10),
NOV = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=11),
DEC = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=12),
CON = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1)
FROM #ProName1
WHERE YEAR(tDate)=2005 And tClassID=1
GROUP BY tClassID我要得到这样的结果,,,,能写简单点吗?
tClassID,
JAN = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=1),
FRI = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=2),
MAR = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=3),
APR = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=4),
MAY = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=5),
JUE = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=6),
JUL = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=7),
AGU = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=8),
SEP = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=9),
OCT = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=10),
NOV = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=11),
DEC = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=12),
CON = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1)
FROM #ProName1
WHERE YEAR(tDate)=2005 And tClassID=1
GROUP BY tClassID我要得到这样的结果,,,,能写简单点吗?
set @s='select 版块=tClassID'
select @s=@s+','+quotename(convert(varchar,dmonth)+'月份')+'=SUM(case when MONTH(tDate)='+convert(varchar,dmonth)+' then tNum else 0 end)'
From (select distinct MONTH(tDate) dmonth from #ProName WHERE YEAR(tDate)='2005') a
select @s=@s+' From #ProName WHERE YEAR(tDate)=''2005'' GROUP BY tClassID'
exec(@s)
/*
版块 3月份 4月份 5月份 6月份
1 12 330 12 0
2 44 440 44 0
3 44 0 44 664
*/
pivot (sum(tnum) for tdate in ([2005-03-02],[2005-03-03])) as a结果:
tclassid 2005-03-02 2005-03-03
1 12 NULL
2 NULL 44
3 NULL NULL但是得出每个月的还没想出来
pivot是sql2005的函数
goselect * from #proname1
pivot (sum(tnum) for M in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) as a用一个临时表可以实现
pivot
我的是SQL2000
INSERT INTO #ProName SELECT 1,'2005-03-02',12
INSERT INTO #ProName SELECT 2,'2005-03-03',44
INSERT INTO #ProName SELECT 3,'2005-03-30',44
INSERT INTO #ProName SELECT 1,'2005-05-02',12
INSERT INTO #ProName SELECT 2,'2005-05-03',44
INSERT INTO #ProName SELECT 3,'2005-05-30',44
INSERT INTO #ProName SELECT 1,'2005-04-02',330
INSERT INTO #ProName SELECT 2,'2005-04-03',440
INSERT INTO #ProName SELECT 3,'2005-06-30',664SELECT top 1
tClassID,
JAN = (SELECT SUM(tNum) FROM #ProName WHERE MONTH(tDate)=1),
FRI = (SELECT SUM(tNum) FROM #ProName WHERE MONTH(tDate)=2),
MAR = (SELECT SUM(tNum) FROM #ProName WHERE MONTH(tDate)=3),
APR = (SELECT SUM(tNum) FROM #ProName WHERE MONTH(tDate)=4),
MAY = (SELECT SUM(tNum) FROM #ProName WHERE MONTH(tDate)=5),
JUE = (SELECT SUM(tNum) FROM #ProName WHERE MONTH(tDate)=6),
JUL = (SELECT SUM(tNum) FROM #ProName WHERE MONTH(tDate)=7),
AGU = (SELECT SUM(tNum) FROM #ProName WHERE MONTH(tDate)=8),
SEP = (SELECT SUM(tNum) FROM #ProName WHERE MONTH(tDate)=9),
OCT = (SELECT SUM(tNum) FROM #ProName WHERE MONTH(tDate)=10),
NOV = (SELECT SUM(tNum) FROM #ProName WHERE MONTH(tDate)=11),
DEC = (SELECT SUM(tNum) FROM #ProName WHERE MONTH(tDate)=12),
CON = (SELECT SUM(tNum) FROM #ProName )
FROM #ProName
WHERE YEAR(tDate)=2005
GROUP BY tClassID