查询一个月的数据，按天分组如何查_Sql Server

查询一个月的数据，按天分组怎么查
表SDD
ID mmTime NO
1 2012-1-11 12

2 2012-1-11 4

3 2012-1-15 27

4 2012-1-16 8

如何如下效果：查询结果按天分组如下

1 2 3 .... 11 12 13 14 15 16 ...31 <------代表日期

16 27 8 <------代表数量（每天的数量相加）

------解决方案--------------------

SQL code

SELECT * INTO #TRanen FROM (SELECT 1 AS ID, CONVERT(DATETIME,'2012-1-11') AS mmTime,12 as [NO] UNION ALLSELECT 2, '2012-1-11',4 UNION ALLSELECT 3, '2012-1-15',27 UNION ALLSELECT 4, '2012-1-16',8 ) TDECLARE @SQL VARCHAR(500)SELECT @SQL = ISNULL(@SQL + ',' ,'') + '[' + CONVERT(NVARCHAR(10),MMTIME,23)+']' FROM #TRanen GROUP BY CONVERT(NVARCHAR(10),MMTIME,23)EXEC('SELECT * FROM #TRanen A PIVOT (SUM([NO]) FOR MMTIME IN([email protected]+')) B')DROP TABLE #TRanen
------解决方案--------------------
探讨
ID mmTime NO UType
1 2012-1-11 12 橘子

2 2012-1-11 4 橘子

3 2012-1-15 27 橘子

4 2012-1-16 8 橘子

5 2012-1-16 8 香蕉

如何如下效果： 查询结果按天分组如下

name 1 2 3 .... 11 12 13 14 15 16 ...31 <------代表日期

橘……

------解决方案--------------------
SQL codeIF OBJECT_ID(N'tempdb..#TRanen') IS NOT NULLDROP TABLE #TRanenGOSELECT * INTO #TRanen FROM (SELECT 1 AS ID, CONVERT(DATETIME,'2012-1-11') AS mmTime,12 as [NO] UNION ALLSELECT 2, '2012-1-11',4 UNION ALLSELECT 3, '2012-1-15',27 UNION ALLSELECT 4, '2012-1-16',8 ) TGODECLARE @SQL VARCHAR(500)SELECT @SQL = ISNULL(@SQL + ',' ,'') + QUOTENAME(CONVERT(VARCHAR(10),MMTIME,120))FROM (SELECT DISTINCT MMTIME = CONVERT(NVARCHAR(10),MMTIME,120) FROM #TRanen) AAEXEC('SELECT ID,[email protected]+' FROM #TRanen A PIVOT (SUM([NO]) FOR MMTIME IN([email protected]+')) B')DROP TABLE #TRanen/*ID    2012-01-11    2012-01-15    2012-01-161    12    NULL    NULL2    4    NULL    NULL3    NULL    27    NULL4    NULL    NULL    8*/