如何合并数据到一行？

[albert_skynet]

create table test
( 
  Uid int identity(1,1),
  UserName varchar(20),
  DateFlag varchar(10),
  Part varchar(10),
  LoginSum int
)
insert into test 
select 'admin','200910','as',15 union all
select 'admin','200911','ad',25 union all
select 'admin','200909','ae',20 union all
select 'test','200909','af',31 union all
select 'test','200910','ag',31 union all
select 't','200910','ag',11

select * from test的结果是
UID     UserName  DateFlag  Part    LoginSum
1    admin    200910    as    15
2    admin    200911    ad    25
3    admin    200909    ae    20
4    test    200909    af    31
5    test    200910    ag    31
6    t       200910    ag    11

查询LoginSum最大的前两个记录，按用户名分组，将用户名相同的记录合并到一列

目标结果： 
UserName  DateFlag  Part    LoginSum  DateFlag  Part    LoginSum 
admin 200911 ad 25 200909 ae 20 
test  200909 ag 31 200910 ag 31 
t    200910 ag 11 null null null 

使用
select * from test A where UID in (select top 2 UID from test 
where Username=A.userName order by LoginSum desc) 

可以将LoginSum最大的前两行记录查询到，如何将记录合并成一列？

[winstonbonaparte]

這樣的例子在上面好多的，你搜索一下吧

[SQL77]

不是有写了么?

[--小F--]

把分组前2放入临时表

然后再用上午写的那个语句来做

[nalnait]

1 admin 200910 as 15 呢?

[albert_skynet]

上午的是dateFlag分的，现在不用这个条件，只有用户名是确定的，其它没有确定的列，

[百年树人]

select a.UserName,a.DateFlag,a.Part,a.LoginSum,b.DateFlag,b.Part,b.LoginSum
from
(
select *,px=(select count(1)+1 from test where username=t.username and uid>t.uid) from test t
) a
left join 
(
select *,px=(select count(1)+1 from test where username=t.username and uid>t.uid) from test t
) b
on a.username=b.username and a.px=b.px-1
where a.px=1

/**
UserName             DateFlag   Part       LoginSum    DateFlag   Part       LoginSum    
-------------------- ---------- ---------- ----------- ---------- ---------- ----------- 
admin                200909     ae         20          200911     ad         25
test                 200910     ag         31          200909     af         31
t                    200910     ag         11          NULL       NULL       NULL

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

[--小F--]

---2005  try
select
  a.UserName,a.DateFlag,a.Part,a.LoginSum,b.DateFlag,b.Part,b.LoginSum
from
 (select *,px=row_number()over(partition by username order by uid) from test t) a
left join 
 (select *,px=px=row_number()over(partition by username order by uid) from test t) b
on
 a.username=b.username and a.px=b.px-1
where a.px=1

[--小F--]

---修改
select
  a.UserName,a.DateFlag,a.Part,a.LoginSum,b.DateFlag,b.Part,b.LoginSum
from
 (select *,px=row_number()over(partition by username order by uid) from test t) a
left join 
 (select *,px=row_number()over(partition by username order by uid) from test t) b
on
 a.username=b.username and a.px=b.px-1
where a.px=1

[百年树人]

[Quote=引用 6 楼 josy 的回复:]
SQL codeselect a.UserName,a.DateFlag,a.Part,a.LoginSum,b.DateFlag,b.Part,b.LoginSumfrom
(select*,px=(selectcount(1)+1from testwhere username=t.usernameand uid>t.uid)from test t
) aleftjoin
(select*,p?-
[/Quote]
是“LoginSum最大的前两行”，修正一下

select a.UserName,a.DateFlag,a.Part,a.LoginSum,b.DateFlag,b.Part,b.LoginSum
from
(
select *,px=(select count(1)+1 from test where username=t.username and (LoginSum>t.LoginSum or LoginSum=t.LoginSum and uid<t.uid)) from test t
) a
left join 
(
select *,px=(select count(1)+1 from test where username=t.username and (LoginSum>t.LoginSum or LoginSum=t.LoginSum and uid<t.uid)) from test t
) b
on a.username=b.username and a.px=b.px-1
where a.px=1

/**
UserName             DateFlag   Part       LoginSum    DateFlag   Part       LoginSum    
-------------------- ---------- ---------- ----------- ---------- ---------- ----------- 
admin                200911     ad         25          200909     ae         20
test                 200909     af         31          200910     ag         31
t                    200910     ag         11          NULL       NULL       NULL

（所影响的行数为 3 行）

**/

[albert_skynet]

josy正解