SQL的三行变一行怎么写？

[qilinshu]

如图所示，我按照员工工资排序，高的在上，低的在下

如何3行变1行，求高人指教



员工ID 员工名字 员工工资
111 x1 4000
222 x2 3000
333 x3 2000

员工1的ID 员工1名字 员工1工资 员工2的ID 员工2的名字 员工2的工资 员工3的ID 员工3的名字 员工3的工资
111 x1 4000 222 x2 3000 333 x3 2000

请大家帮我想想，急！！！ 多谢

[永生天地]

[sql server] 行转列问题总结1 - 行转列
http://blog.csdn.net/xys_777/archive/2010/06/22/5685953.aspx

[olyhome]

路过,,,,

[Mr_Nice]

[Quote=引用 1 楼 xys_777 的回复:]

[sql server] 行转列问题总结1 - 行转列
http://blog.csdn.net/xys_777/archive/2010/06/22/5685953.aspx
[/Quote]

up

[中国风]

?
use Tempdb
go
--> -->

declare @ table([员工ID] int,[员工名字] nvarchar(2),[员工工资] Datetime)
Insert @
select 111,N'x1','4000' union all
select 222,N'x2','3000' union all
select 333,N'x3','2000'


Select *
from @ AS a,@ AS b ,@ AS c
WHERE a.员工ID=111 AND b.员工ID=222 AND c.员工ID=333

[中国风]

固定三行顯示為

use Tempdb
go
--> --> 
 
declare @ table([员工ID] int,[员工名字] nvarchar(2),[员工工资] Datetime)
Insert @
select 111,N'x1','4000' union all
select 222,N'x2','3000' union all
select 333,N'x3','2000'

;WITH t1
AS
(
SELECT *,row=ROW_NUMBER()OVER(ORDER BY [员工ID]) FROM @
)
Select *
from t1 AS a
LEFT JOIN t1 AS b ON a.row=b.row-1 
LEFT JOIN t1 AS c ON b.row=c.row-1
WHERE  a.row%3=1

[qilinshu]

谢谢roy_88

但是如果SELECT *,row=ROW_NUMBER()OVER(ORDER BY [员工ID]) FROM @ 后面加上order by 就不行。。。。。。

就报错。我是根据工资排序的，囧。

[laoyingisme]

用keyword union

[rmljoe]

[Quote=引用 6 楼 qilinshu 的回复:]

谢谢roy_88

但是如果SELECT *,row=ROW_NUMBER()OVER(ORDER BY [员工ID]) FROM @ 后面加上order by 就不行。。。。。。

就报错。我是根据工资排序的，囧。
[/Quote]

报错，应该是用的数据库是2000，不是2005的，可以用临时表

use Tempdb
go

 
declare @ table([员工ID] int,[员工名字] nvarchar(2),[员工工资] Datetime)
Insert @
select 111,N'x1','4000' union all
select 222,N'x2','3000' union all
select 333,N'x3','2000'

select identity(int,1,1) as row,* into # from @ order by [员工工资]

Select *
from # AS a
LEFT JOIN # AS b ON a.row=b.row-1 
LEFT JOIN # AS c ON b.row=c.row-1
WHERE  a.row%3=1

drop table #

[weiweilongke]

想必楼主是要这个样子的吧，用一个游标来完成呀，放心，已测过了

declare @id nvarchar(10),@nam nvarchar(10),@mon float,@myall nvarchar(30)
set @myall=''
declare  mycur cursor for select id,nam,mon from gongzi order by id
open mycur
FETCH NEXT FROM mycur INTO @id,@nam,@mon
WHILE @@FETCH_STATUS = 0
BEGIN
set @myall=@myall+@id+@nam+convert(varchar,@mon)
FETCH NEXT FROM mycur INTO @id,@nam,@mon
END
CLOSE mycur
DEALLOCATE mycur
select @myall