把一个表的行列转换的存储过程怎么写

[baoshan]

有一表
user{
userid as varchar(10);
username as varchar(10);
userbirth as date;
userage as int;
}
写一存储过程,要求把该表的行列转换

[filebat]

http://community.csdn.net/expert/FAQ/FAQ_Index.asp?id=207191

[paoluo]

写出你根据数据要转之后的形式，可以写一个出来。

[zlp321002]

使用交叉表:
CREATE PROCEDURE RobinCrossTable --交叉表生成器
--(Select Distinct SuppCode As SCode,MCode,PurQtyRate As Rate
-- From MtSPM A Inner Join MtSPD B On A.ID=B.MtSuppPriceM_ID
-- Inner Join Suppliers C On A.Supp_ID=C.ID
-- Where A.Active_KID=120 And AduitPass=1) D
@vSourceTAB As Varchar(2000), --数据来源表,可以为表,视图,或者SQL语句(要用括号以及别名:如上注释段)
@vGroupbyField As Varchar(50), --被selct Group By 要显示出来的,可以多个字段(记录可以有空值)
@vTransFormCol As Varchar(50), --交叉表中的合计等函数计算值的字段
@vFunction As Varchar(50)=' Sum', --默认值,交叉表中的函数,也可以是' 2*Sum'的计算公式
@vPivotCol As Varchar(50), --要转换成列的字段,唯一列,可以是表达式'Field1+Field2'(记录可以有空值)
@vStrWhere As Varchar(500) =Null, --Where 约束条件,可以为空
@vSumstr As Varchar(50) --最后一列的合计总和
AS
--重要提示:@StrSql的Largest size allowed Is 8000,因此尽量将少的字段内容转换为列
Declare @StrSql As Varchar(8000) --//总的SQL语句
Declare @StrSum As Varchar(3000) --//列合计
Declare @pCols As Varchar(100)
Declare @StrWhere As Varchar(500)
Execute('Declare CursorCross Cursor For
Select Distinct ' + @vPivotCol + ' From ' +@vSourceTAB +' Order By ' + @vPivotCol + ' For Read only ')
Begin
Set Nocount On
Set @StrSql =''
Set @StrSum=''
Set @pCols=''
IF Rtrim(Ltrim(IsNull(@vStrWhere,''))) <> ''
Begin
Set @StrWhere=' Where ' + @vStrWhere + ' '
End
Else
Set @StrWhere=''
Open CursorCross
While (0=0)
Begin
Fetch Next From CursorCross Into @pCols
IF (@@Fetch_Status<>0) Break
IF @pCols Is Null --//不为空值,
Set @pCols='Null'
--为了防止新创建的列的标题名称,与@vGroupbyField中的字段重名,
--新创建的列的标题名称都增加前缀[@vGroupbyField.新创建的列的标题名称]
--因Sql长度限制Max=8000,由源数据控制字段值不能为Null,因此这里不再检验值是否为Null
Set @StrSql=@StrSql +',' + @vFunction +
'(Case '+@vPivotCol+' When ''' + @pCols+ ''' Then '+@vTransFormCol +' Else 0 End) As '+
'['+@pCols+']'
-- print @StrSql
--因长度限制,不计算列间之和
-- Set @StrSum=@StrSum + '+IsNull(A.' + '['+Left(@vPivotCol,1)+@pCols+']' +',0)'
End
Set @StrSql = @StrSql+','+@vFunction+'('+@vTransFormCol+') as '+@vSumstr
Set @StrSql = ' Select ' + @vGroupByField + ' ' +@StrSql + ' From ' +@vSourceTAB+ ' ' + @StrWhere +
' Group By ' + @vGroupByField
--列向合计 为字段名'TotalSum'
-- Set @StrSql ='Select A.*,(0' +@StrSum + ') As TotalSum From (' + @StrSql +') As A'
Set @StrSql ='Select A.* From ('+@StrSql+') As A'
Execute(@StrSql)
IF @@Error <>0
Return @@Error
Close CursorCross
Deallocate CursorCross
Return 0
End
GO

[zjcxc]

--示例数据
create table [user](
userid varchar(10),
username varchar(10),
userbirth datetime,
userage int
)

insert [user] select 'aa','清华同方',getdate(),10
union all select 'bb','北大方正',getdate(),20
go

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+'=''userid='''''+name+''''''''
,@s3=@s3+'
,@'+@i+'=@'+@i+'+'',[''+cast([userid] as varchar)+'']=''''''+cast(['+name+'] as varchar)+'''''''''
,@s4=@s4+',@'+@i+'=''select ''+@'+@i
,@s5=@s5+'+'' union all ''+@'+@i
,@i=cast(@i as int)+1
from syscolumns
where object_id('user')=id and colid>1
order by colid

select @s1=stuff(@s1,1,1,'')
,@s2=stuff(@s2,1,1,'')
,@s3=stuff(@s3,1,4,'')
,@s4=stuff(@s4,1,1,'')
,@s5=stuff(@s5,1,15,'')

exec('declare '+@s1+'
select '+@s2+'
select '+@s3+'
from [user]
select '+@s4+'
exec('+@s5+')')
go

--删除测试
drop table [user]

/*--结果

userid aa bb
--------- ------------------ ------------------
username 清华同方 北大方正
userbirth 07 9 2005 5:11PM 07 9 2005 5:11PM
userage 10 20

（所影响的行数为 3 行）
--*/