哪位老兄能帮我用存储过程变横向表为纵向表？
哪位老兄能帮我用存储过程变横向表为纵向表？
例如：
A B C D E
-----------------------------
1 2 3 4 5
6 7 8 9 10
11 12 13 14 15
16 17 18 19 20
......
改为：
I II III IV ......
_____________________________
1 6 11 16
2 7 12 17
3 8 13 18
4 9 14 19
5 10 15 20 ......
列名可以自定义！
多谢，拜托！
[解决办法]
/*------一个标准的统计交叉表过程-----------*/
if exists (select * from dbo.sysobjects where id = object_id(N '[dbo].[p_qry] ') and OBJECTPROPERTY(id, N 'IsProcedure ') = 1)
drop procedure [dbo].[p_qry]
GO
/*--生成交叉表的简单通用存储过程
根据指定的表名,纵横字段,统计字段,自动生成交叉表
并可根据需要生成纵横两个方向的合计
注意,横向字段数目如果大于纵向字段数目,将自动交换纵横字段
如果不要此功能,则去掉交换处理部分
--邹建 2004.06--*/
/*--调用示例
exec p_qry 'syscolumns ', 'id ', 'colid ', 'colid ',1,1
--*/
create proc p_qry
@TableName sysname, --表名
@纵轴 sysname, --交叉表最左面的列
@横轴 sysname, --交叉表最上面的列
@表体内容 sysname, --交叉表的数数据字段
@是否加横向合计 bit,--为1时在交叉表横向最右边加横向合计
@是否家纵向合计 bit --为1时在交叉表纵向最下边加纵向合计
as
declare @s nvarchar(4000),@sql varchar(8000)
--判断横向字段是否大于纵向字段数目,如果是,则交换纵横字段
set @s= 'declare @a sysname
if(select case when count(distinct [ '+@纵轴+ ']) <count(distinct [ '+@横轴+ ']) then 1 else 0 end
from [ '+@TableName+ '])=1
select @a=@纵轴,@纵轴=@横轴,@横轴=@a '
exec sp_executesql @s
,N '@纵轴 sysname out,@横轴 sysname out '
,@纵轴 out,@横轴 out
--生成交叉表处理语句
set @s= '
set @s= ' ' ' '
select @s=@s+ ' ',[ ' '+cast([ '+@横轴+ '] as varchar)+ ' ']=sum(case [ '+@横轴
+ '] when ' ' ' ' ' '+cast([ '+@横轴+ '] as varchar)+ ' ' ' ' ' ' then [ '+@表体内容+ '] else 0 end) ' '
from [ '+@TableName+ ']
group by [ '+@横轴+ '] '
exec sp_executesql @s
,N '@s varchar(8000) out '
,@sql out
--是否生成合计字段的处理
declare @sum1 varchar(200),@sum2 varchar(200),@sum3 varchar(200)
select @sum1=case @是否加横向合计
when 1 then ',[合计]=sum([ '+@表体内容+ ']) '
else ' ' end
,@sum2=case @是否家纵向合计
when 1 then '[ '+@纵轴+ ']=case grouping([ '
+@纵轴+ ']) when 1 then ' '合计 ' ' else cast([ '
+@纵轴+ '] as varchar) end '
else '[ '+@纵轴+ '] ' end
,@sum3=case @是否家纵向合计
when 1 then ' with rollup '
else ' ' end
--生成交叉表
exec( 'select '+@sum2+@sql+@sum1+ '
from [ '+@TableName+ ']
group by [ '+@纵轴+ '] '+@sum3)
go
[解决办法]
if exists (select * from dbo.sysobjects
where id = object_id(N '[dbo].[p_zj] ') and OBJECTPROPERTY(id, N 'IsProcedure ') = 1)
drop procedure [dbo].[p_zj]
GO
/*--行列互换的通用存储过程 ： 将指定的表,按指定的字段进行行列互换
--邹建 2004.04--
--使用示例
--测试数据
create table 表(类别 varchar(10),男性 decimal(20,1),女性 decimal(20,1))
insert 表 select '小说 ',38.0,59.2
union all select '散文 ',18.9,30.6
union all select '哲学 ',16.2,10.2
--要求转换结果
/*
性别 小说 散文 哲学
---- ----- ----- -----
男性 38.0 18.9 16.2
女性 59.2 30.6 10.2
*/
--调用存储过程
exec p_zj '表 ', '类别 ', '性别 '
--删除测试
drop table 表
*/
create proc p_zj
@tbname sysname, --要处理的表名
@fdname sysname, --做为转换的列名
@new_fdname sysname= ' ' --为转换后的列指定列名
as
declare @s1 varchar(8000),@s2 varchar(8000)
,@s3 varchar(8000),@s4 varchar(8000),@s5 varchar(8000)
,@i varchar(10)
select @s1= ' ',@s2= ' ',@s3= ' ',@s4= ' ',@s5= ' ',@i= '0 '
select @s1=@s1+ ',@ '+@i+ ' varchar(8000) '
,@s2=@s2+ ',@ '+@i+ '= ' ' '+case isnull(@new_fdname, ' ') when ' ' then ' '
else @new_fdname+ '= ' end+ ' ' ' ' ' '+name+ ' ' ' ' ' ' ' '
-- ,@s2=@s2+ ',@ '+@i+ '= ' '性别= ' ' ' ' '+name+ ' ' ' ' ' ' ' '
,@s3=@s3+ '
select @ '+@i+ '=@ '+@i+ '+ ' ',[ ' '+[ '+@fdname+ ']+ ' ']= ' '+cast([ '+name+ '] as varchar) from [ '+@tbname+ '] '
,@s4=@s4+ ',@ '+@i+ '= ' 'select ' '+@ '+@i
,@s5=@s5+ '+ ' ' union all ' '+@ '+@i
,@i=cast(@i as int)+1
from syscolumns
where object_id(@tbname)=id and name <> @fdname
select @s1=substring(@s1,2,8000)
,@s2=substring(@s2,2,8000)
,@s4=substring(@s4,2,8000)
,@s5=substring(@s5,16,8000)
exec( 'declare '+@s1+ '
select '+@s2+@s3+ '
select '+@s4+ '
exec( '+@s5+ ') ')
go
[解决办法]
--测试环境
DECLARE @T TABLE( A INT, B INT, C INT, D INT ,E INT)
INSERT INTO @T SELECT 1,2,3,4,5
UNION ALL SELECT 6,7,8,9,10
UNION ALL SELECT 11,12,13,14,15
UNION ALL SELECT 16,17,18,19,20
--测试语句
SELECT * FROM
(
SELECT ROW_ID,TA,TB FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY A) AS ROW_ID,A,B,C,D,E FROM @T
) TA
UNPIVOT
(TA FOR TB IN
([A],[B],[C],[D],[E])
)
AS UNPIT
) TA
PIVOT
(MAX(TA)
FOR ROW_ID IN ([1],[2],[3],[4])
) AS PIT
--测试结果
/*
(4 行受影响)
TB 1 2 3 4
----------------------------------A 1 6 11 16
B 2 7 12 17
C 3 8 13 18
D 4 9 14 19
E 5 10 15 20
(5 行受影响)
*/
