求SQL2000 行转列代码
数据表格式：
ID 凭据编号 登记日期 联系人 联系电话 所属部门 备注
1 2013050201 2013-05-02 张三 110 销售部
2 2013050202 2013-05-02 李斯 110 销售部
3 2013050301 2013-05-03 王五 119 开发部
4 2013050401 2013-05-04 赵六 行政部
要把数据生成如下格式
销售部五月的数据（查询销售部）查询出属于销售部的登记数量，跟进月(day)进行统计
日期 1 2 3 4 5 6 7 8 9 10 11 .... 31 合计
数据 0 2 0 0 0 0 0 0 0 0 0 0 .... 0 2
查询需求：查询依据指定部门，指定月份，查询出指定月每天的登记量，如果没有显示0，最后还有个合计是当月的登记量，请问这个sql该怎么写。数据库是SQL2000.
请教各位大侠，可以达到这个效果吗？ sql 数据库 数据 需求
[解决办法]
if OBJECT_ID('tempdb..#temp', 'u') is not null drop table #temp;
go
create table #temp( [ID] int, [凭据编号] varchar(100), [登记日期] DATETIME, [联系人] varchar(100), [联系电话] varchar(100), [所属部门] varchar(100));
insert #temp
select '1','2013050201','2013-05-02','张三','110','销售部' union all
select '2','2013050202','2013-05-02','李斯','110','销售部' union all
select '3','2013050301','2013-05-03','王五','119','开发部' union all
select '4','2013050401','2013-05-04','赵六',null,'行政部'
--SQL:
DECLARE @department NVARCHAR(100), @month CHAR(6), @sql NVARCHAR(MAX), @collist NVARCHAR(MAX)
SELECT @department = N'销售部', @month = '201305'
SET @collist = ''
SELECT @collist = @collist + QUOTENAME(number+1)+' = SUM(CASE DAY(AllMonthDay) WHEN '+ LTRIM(number+1) +' THEN CNT END),'
FROM master..spt_values
WHERE type = 'p'
AND number < DATEDIFF(DAY, @month+'01', DATEADD(MONTH,1,@month+'01'))
SET @collist = @collist + '[总计] = SUM(CASE WHEN AllMonthDay IS NULL THEN cnt END)'
SET @sql = N'
SELECT '+ @collist +'
FROM
(
SELECT A.AllMonthDay, CNT = COUNT(b.id)
FROM
(
SELECT AllMonthDay=DATEADD(DAY, NUMBER, '''+ @month +'''+''01'') FROM master..spt_values
WHERE type = ''p''
AND number < DATEDIFF(DAY, '''+ @month +'''+''01'', DATEADD(MONTH,1,'''+ @month +'''+''01''))
) a
LEFT JOIN #temp b
ON a.AllMonthDay = b.[登记日期]
AND B.[所属部门] = N'''+ @department +'''
GROUP BY A.AllMonthDay
WITH ROLLUP
) T
'
--PRINT @sql
EXEC(@sql)
/*
12345678910111213141516171819202122232425262728293031总计
02000000000000000000000000000002
*/
被截断了
改成VARCHAR(MAX)
DECLARE @department NVARCHAR(100), @month CHAR(6), @sql NVARCHAR(max), @collist NVARCHAR(2000)
SELECT @department = N'珠海店', @month = '201308'
SET @collist = ''
SELECT @collist = @collist + QUOTENAME(number+1)+' = SUM(CASE DAY(AllMonthDay) WHEN '+ LTRIM(number+1) +' THEN CNT END),'
FROM master..spt_values WHERE type = 'p' and number < DATEDIFF(DAY,@month+'01',DATEADD(MONTH,1,@month+'01'))
SET @collist = @collist + '[总计] = SUM(CASE WHEN AllMonthDay IS NULL THEN cnt END)'
SET @sql = N'
SELECT '+ @collist +'
FROM
(
SELECT A.AllMonthDay, CNT = COUNT(b.[CIID])
FROM
(
SELECT AllMonthDay=DATEADD(DAY, NUMBER, '''+ @month +'''+''01'') FROM master..spt_values
WHERE type = ''p'' and number < DATEDIFF(DAY, ''' + @month + '''+''01'', DATEADD(MONTH,1,''' + @month + '''+''01''))
)a
LEFT JOIN CarInsuranceTL b
ON a.AllMonthDay = CONVERT(CHAR(10),b.[CIInsuranceDate],120)
AND B.[Dealerships] = N'''+ @department +'''
GROUP BY A.AllMonthDay
WITH ROLLUP
) T
'
--PRINT @sql
EXEC(@sql)