sql 2005固定列实现过程:
select category,isnull(south,0)as south,isnull(northeast,0)as northeast,isnull(midwest,0) as midwest,isnull(west,0) as west,isnull(south,0)+isnull(northeast,0)+isnull(midwest,0)+isnull(west,0) as total from (select category,amount,region from rawdata)as sq
pivot(sum(amount) for region in ([south],[northeast],[MidWest],[West])) as pt
结果:
category south northeast midwest West total
X 165 0 24 36 225
Y 287 181 38 0 506
Z 33 55 83 44 215
sql 2005动态实现过程:
DECLARE @XColumns NVARCHAR(1024)
SET @XColumns=''
SELECT @XColumns=@XColumns+[a].[column]+', '
FROM (SELECT DISTINCT Region as [column] FROM RawData) as aSET @XColumns=LEFT(@XColumns,len(@XColumns)-1)
SET @XColumns='SELECT Category,'
+@XColumns
+' FROM (select category,region,amount from rawdata) as b PIVOT (sum(amount) for region in ('+@XColumns+')) as pt'
exec sp_executesql @XColumns
结果:
cateogry midwest northeast south west
X 24 NULL 165 36
Y 38 181 287 NULL
Z 83 55 33 44
问题是:如果将Null改为0再加上total和固定列显示的效果一样,谢谢
select category,isnull(south,0)as south,isnull(northeast,0)as northeast,isnull(midwest,0) as midwest,isnull(west,0) as west,isnull(south,0)+isnull(northeast,0)+isnull(midwest,0)+isnull(west,0) as total from (select category,amount,region from rawdata)as sq
pivot(sum(amount) for region in ([south],[northeast],[MidWest],[West])) as pt
结果:
category south northeast midwest West total
X 165 0 24 36 225
Y 287 181 38 0 506
Z 33 55 83 44 215
sql 2005动态实现过程:
DECLARE @XColumns NVARCHAR(1024)
SET @XColumns=''
SELECT @XColumns=@XColumns+[a].[column]+', '
FROM (SELECT DISTINCT Region as [column] FROM RawData) as aSET @XColumns=LEFT(@XColumns,len(@XColumns)-1)
SET @XColumns='SELECT Category,'
+@XColumns
+' FROM (select category,region,amount from rawdata) as b PIVOT (sum(amount) for region in ('+@XColumns+')) as pt'
exec sp_executesql @XColumns
结果:
cateogry midwest northeast south west
X 24 NULL 165 36
Y 38 181 287 NULL
Z 83 55 33 44
问题是:如果将Null改为0再加上total和固定列显示的效果一样,谢谢
解决方案 »
- 如何查找某列相同值?请高手指教!
- 怎么加一整数列啊
- 记录合并成一个字符串
- 我从数据库A中导出store procedure生成的脚本,发现有SET ANSI_NULLS OFF,若我运行此脚本到数据库B中,请问B中会不会也使ANSI_NULLS 置为OF
- 关于读取EXCEL表格填充DataGrid
- 如何将数据库中一个字段对应的多行记录合成一行,插入到另一个字段中!!
- 请教一个有关子查询别名的问题
- 一直搞不明白的问题。。。。希望对数据库有深入研究的兄第。。可以帮我解决我的疑惑。。。
- 求助:如何用vc通过ADO方式以windown验证方式连接SQLserver2000
- 500W数据量的表,查询其中一条记录,需要控制在30毫秒左右
- 统计
- 如何查找编号最小的所有数据
DECLARE @Fields NVARCHAR(1024)
SET @Fields=''
SELECT
@Fields=@Fields+',isnull('+[a].[column]+',0) '
,@XColumns=isnull(@XColumns+',','')+[a].[column]
FROM (SELECT DISTINCT Region as [column] FROM RawData) as a
SET @XColumns='SELECT Category'
+@Fields
+' FROM (select category,region,amount from rawdata) as b PIVOT (sum(amount) for region in ('+@XColumns+')) as pt'
exec sp_executesql @XColumns
DECLARE @Fields NVARCHAR(1024)
DECLARE @Total NVARCHAR(1024)SET @Fields=''
SELECT
@Fields=@Fields+',isnull('+[a].[column]+',0) '
,@Total=isnull(@Total+'+','')+'isnull('+[a].[column]+',0) '
,@XColumns=isnull(@XColumns+',','')+[a].[column]
FROM (SELECT DISTINCT Region as [column] FROM RawData) as a
SET @XColumns='SELECT Category'
+@Fields
+','+@Total+' as total'
+' FROM (select category,region,amount from rawdata) as b PIVOT (sum(amount) for region in ('+@XColumns+')) as pt'
exec sp_executesql @XColumns