求一个sql语句，关于统计的功能。

[baiduu]

orderid province time
13359 北京 2013/08/02
14327 北京 2013/07/16
19284 北京 2013/06/02
13342 广东 2013/06/04
12332 四川 2013/05/03
23333 四川 2013/04/02
......
......


现在想按月份统计一下各个省份的订单量，生成类似于下面这样的表格：


北京 上海 广东 四川 吉林 广西 山西 ......
2013/04 19 25 30 20 11 12 13 ......
2013/05 18 ......
2013/06 16 ......
......


求教SQL语句，谢谢。

[--小F--]

又见到了行列转换了。

[baiduu]

行列转换，我去搜一下。

[lzw_0736]

WITH a1 (orderid,province,TIME) AS
(
SELECT 13359,'北京','2013/08/02' UNION ALL
SELECT 14327,'北京','2013/07/16' UNION ALL
SELECT 19284,'北京','2013/06/02' UNION ALL
SELECT 13342,'广东','2013/06/04' UNION ALL
SELECT 12332,'四川','2013/05/03' UNION ALL
SELECT 23333,'四川','2013/04/02'
)
SELECT * INTO #temp FROM a1

DECLARE @sql VARCHAR(MAX), @colList VARCHAR(MAX)
SET @colList = STUFF(
(
    SELECT ','+QUOTENAME(province) 
    FROM (SELECT DISTINCT province FROM #temp) t
    FOR XML PATH('')
),1,1,'')
SET @sql = '
    select * 
    from (select TIME,province,1 n from #temp) a
    pivot
    (sum(n) for province in('+ @colList +')) b'
 
EXEC(@sql)

[baiduu]

谢谢亲，不过亲你的代码好像不是按月份统计的。

[lzw_0736]

WITH a1 (orderid,province,TIME) AS
(
SELECT 13359,'北京','2013/08/02' UNION ALL
SELECT 14327,'北京','2013/07/16' UNION ALL
SELECT 19284,'北京','2013/06/02' UNION ALL
SELECT 19284,'北京','2013/06/03' UNION ALL
SELECT 13342,'广东','2013/06/04' UNION ALL
SELECT 12332,'四川','2013/05/03' UNION ALL
SELECT 23333,'四川','2013/04/02'
)
SELECT * INTO #temp FROM a1
 
DECLARE @sql VARCHAR(MAX), @colList VARCHAR(MAX)
SET @colList = STUFF(
(
    SELECT ','+QUOTENAME(province) 
    FROM (SELECT DISTINCT province FROM #temp) t
    FOR XML PATH('')
),1,1,'')
SET @sql = '
    select * 
    from (select CONVERT(char(7),TIME,120) time,province,1 n from #temp) a
    pivot
    (sum(n) for province in('+ @colList +')) b'
  
EXEC(@sql)

[呆呆和笨笨]

declare @strsql varchar(max)
select @strsql='';
select @strsql='select time '
select @strsql=@strsql+',(select COUNT(*) from #a1 where left(convert(varchar(10),time,120),7)=A.time and province='''+province+''') '+province from #a1 group by province
select @strsql=@strsql+' from(select time= left(CONVERT(varchar(10),time,120),7) from #a1 group by left(CONVERT(varchar(10),time,120),7) ) A'
exec(@strsql)

[Andy__Huang]

经典例子总结:http://bbs.csdn.net/topics/240002706