sqlserver2005中，怎样将一行6列的数据转换为3列数据，具体的看问题补充。

现在我有一个存储过程，ALTER proc [dbo].[My_GetMyPayAmount]

@studentId varchar(20)
as
select ContractNew.StudentName,ContractNew.PayAmount,ContractNew.SignDate,ContractReNewal.PayAmount as repayamount,
ContractReNewal.SignDate as repayaddtime,(0-RefundAmount) as RefundAmount,RefundDate
from ContractNew 
left join ContractReNewal on ContractNew.studentId=ContractReNewal.studentId 
left join ContractRefund on ContractNew.studentId=ContractRefund.studentId

where ContractNew.studentId=@studentId，查询后的到的结果是  表头数据
StudentName,PayAmount,SignDate,repayamount,repayaddtime,RefundAmount,RefundDate
实际数据是
张琳,1600.00,2012-02-08 00:00:00.000,1600.00,2012-04-01 00:00:00.000,NULL,NULL
我想让数据显示为
张琳，1600.00,2012-02-08 00:00:00.000,新签
张琳，1600.00,2012-04-01 00:00:00.000,续费
退费金额还没有，所以不显示，如果有的话，再显示出来。

追问：不对啊，有语法错误了。对啊，我改了啊。select StudentName,PayAmount,SignDate from (select ContractNew.StudentName,ContractNew.PayAmount,ContractNew.SignDate,ContractReNewal.PayAmount as repayamount,不是，两个null是退费，因为退费里面没有记录，所以不用显示。若是有记录的话，也是会列出来
张琳，1600.00,2012-04-01 00:00:00.000,退费    这条数据表示   要求退费的学生  退费金额   退费时间   类型

答案：看你写的是存储过程，那就用临时表实现
以下是我的操作你那张表的存储过程，能实现了你要求的才贴上来的
create procedure proc_ContractNew
as
begin
create table #tablea(aname varchar(20),bmoney decimal,cdate datetime,dtext varchar(20))
begin tran
insert into #tablea(aname,bmoney,cdate,dtext)select StudentName,PayAmount,SignDate,'新签' from ContractNew
insert into #tablea(aname,bmoney,cdate,dtext)select StudentName,repayamount,repayaddtime,'续费' from ContractNew
commit tran
select * from #tablea
end

其他：select 1,2,3,7 from 上面的查询结果
union
select 4,5,6,8 from 上面的查询结果; with aa as (
select ContractNew.StudentName,
ContractNew.PayAmount,ContractNew.SignDate,
ContractReNewal.PayAmount as repayamount,
ContractReNewal.SignDate as repayaddtime,
(0-RefundAmount) as RefundAmount,RefundDate

from ContractNew 
left join ContractReNewal 
on ContractNew.studentId=ContractReNewal.studentId
 
left join ContractRefund 
on ContractNew.studentId=ContractRefund.studentId

where ContractNew.studentId=@studentId)

select StudentName,PayAmount,SignDate,'新签'
from aa
union 
select StudentName,repayamount,repayaddtime,'续费'
from aa
union
select StudentName,RefundAmount,RefundDate,'退费'
from aa PROVT好像有这个函数，你可以在SQL-2008中找到 

上一个：在sqlserver2005中怎样设置级联删除
下一个：sqlserver 去两个表的数据