交叉表或行转列的优化问题

[wea1978]

declare @sql varchar(8000)
set @sql = 'select fw'
select @sql = @sql + ',sum(case right((convert(varchar(10),tday,112)),2) when '''+
right((convert(varchar(10),tday,112)),2)+''' then cl end) ['+right((convert(varchar(10),tday,112)),2)+']' from #t_mday
select @sql = @sql+' from #t_mday group by fw'
print(@sql)

其中tday是个日期型,得出的结果（如果有的话）:
fw,1,2,3,4,5,6,7,8,9,10,11....直到月底（标题)
a ....

------------问题是,结果字符串不够长,其实查询的tday实际列只有1-11而已...
为什么会不够长呢?

请各位大侠指教,谢谢!

[niulity]

SQL实现这种类似翻转功能很麻烦，满足不了多维查询的需求，建议充分利用OLAP SERVICES建立cube，使用MDX查询

[wea1978]

愿闻其详...
请楼上指教

[wea1978]

这个东西只是一个应用程序中的存储过程而已...我如果用:
declare @sql varchar(8000)
set @sql = 'select fw'
select @sql = @sql + ',sum(case right((convert(varchar(10),tday,112)),2) when '''+
right((convert(varchar(10),tday,112)),2)+''' then cl end) ['+right((convert(varchar(10),tday,112)),2)+']' from
(select distinct right((convert(varchar(10),tday,112)),2) from #t_mday ) a
select @sql = @sql+' from #t_mday group by fw'
print(@sql)
就通不过了,提示:
No column was specified for column 1 of 'a'.

[WangZWang]

--改为,只显示了到11应该与你的结果只有到11日有关,
--你要实现的是固定的31天的列还是什么?
declare @sql varchar(8000)
set @sql = 'select fw'
select @sql = @sql + ',sum(case day(tday) when '''+
cast(day(tday) as varchar)+''' then cl end) ['+
cast(day(tday) as varchar)+']'
from #t_mday order by day(tday)
select @sql = @sql+' from #t_mday group by fw'
print(@sql)
exec(@sql)

[子陌红尘]

declare @sql varchar(8000)
set @sql = 'select fw'
select @sql = @sql + ',sum(case day(tday) when '+tday+' then cl end) ['+tday+']'
from (select distinct rtrim(day(tday)) tday from #t_mday) a
select @sql = @sql+' from #t_mday group by fw'
print(@sql)

[子陌红尘]

declare @sql varchar(8000)
set @sql = 'select fw'
select @sql = @sql + ',sum(case right((convert(varchar(10),tday,112)),2) when '''+
tday+''' then cl end) ['+tday+']' from (select distinct right((convert(varchar(10),tday,112)),2) tday from #t_mday) a

select @sql = @sql+' from #t_mday group by fw'
print(@sql)

[wea1978]

libin_ftsafe(子陌红尘) 的可以解决,但还没有排序,就是没有从1-30这样排..

[子陌红尘]

declare @sql varchar(8000)
set @sql = 'select fw'
select @sql = @sql + ',sum(case day(tday) when '+rtrim(a.tday)+' then cl end) ['+rtrim(a.tday)+']'
from (select distinct day(tday) tday from #t_mday) a order by a.tday
select @sql = @sql+' from #t_mday group by fw'
print(@sql)

[子陌红尘]

或者：

declare @sql varchar(8000)
set @sql = 'select fw'
select @sql = @sql + ',sum(case right((convert(varchar(10),tday,112)),2) when '''+
tday+''' then cl end) ['+tday+']' from (select distinct right((convert(varchar(10),tday,112)),2) tday from #t_mday) a order by a.tday

select @sql = @sql+' from #t_mday group by fw'
print(@sql)


------------------------------------------------------------------------------------------------------------------------
楼主所遇到的问题是，没有过滤重复的日期记录。

[niulity]

启用mssql的analysis services服务，选择数据源，建立多维数据集(面向某一主题用于分析的多维立方体)
选择一个事实表(具体主题的明细表)，选择要分析的数据字段(如库存、销售额)为度量
楼主这里的日期型可以做为一个日期维度(具有年、季度、月、日四层即可满足大多数要求)
建立其它待分析的维度。。。
处理多维数据集(CUBE)
使用MDX进行OLAP操作(旋转、上卷、下钻、切片、切块)，可以很方便地取出任一维度任一层次，楼主这里可以取“日期维度”的“月”层次置于横(列)轴，产品维度(比如说)的产品分类层置于纵(行)轴，选择销售额度量。即可完成对立方体的旋转、切片、切块操作，如有需要可以上卷日期维度至季度层查询更粗粒度一级的数据。
写了一点个人理解，具体查看有关数据仓库与OLAP资料吧~~~ ^_^

[wea1978]

加个日期合计怎样?
比如:
fw,1,2,3,4,5,6,7,8,9,10,11....直到月底（标题),合计

[子陌红尘]

select @sql = @sql+' from #t_mday group by fw'

==》

select @sql = @sql+',合计=sum(cl) from #t_mday group by fw'