如何合并指定行的数据到一行？

[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 

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

如何输出dateflag是200911和200910的数据，合并为一行
目标结果：
UserName DateFlag Part LoginSum DateFlag Part LoginSum
admin 200911 ad 25 200910 as 15
test null null null 200910 ag 31

[dawugui]

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 

go

select UserName, 
  max(case DateFlag when '200911' then DateFlag else null end) DateFlag,
  max(case DateFlag when '200911' then Part else null end) Part,
  max(case DateFlag when '200911' then LoginSum else null end) LoginSum,
  max(case DateFlag when '200910' then DateFlag else null end) DateFlag,
  max(case DateFlag when '200910' then Part else null end) Part,
  max(case DateFlag when '200910' then LoginSum else null end) LoginSum
from test
group by UserName

drop table test

/*
UserName             DateFlag   Part       LoginSum    DateFlag   Part       LoginSum    
-------------------- ---------- ---------- ----------- ---------- ---------- ----------- 
admin                200911     ad         25          200910     as         15
test                 NULL       NULL       NULL        200910     ag         31

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

[guguda2008]

[Quote=引用 1 楼 dawugui 的回复:]
SQL codecreatetable test
(
Uidintidentity(1,1),
UserNamevarchar(20),
DateFlagvarchar(10),
Partvarchar(10),
LoginSumint
)insertinto testselect'admin','200910','as',15unionallselect'admin','200911','ad',25unionallselect'admin','200909','ae',20unionallselect'test','200909','af',31unionallselect'test','200910','ag',31goselect UserName,max(case DateFlagwhen'200911'then DateFlagelsenullend) DateFlag,max(case DateFlagwhen'200911'then Partelsenullend) Part,max(case DateFlagwhen'200911'then LoginSumelsenullend) LoginSum,max(case DateFlagwhen'200910'then DateFlagelsenullend) DateFlag,max(case DateFlagwhen'200910'then Partelsenullend) Part,max(case DateFlagwhen'200910'then LoginSumelsenullend) LoginSumfrom testgroupby UserNamedroptable test/*
UserName DateFlag Part LoginSum DateFlag Part LoginSum
-------------------- ---------- ---------- ----------- ---------- ---------- -----------
admin 200911 ad 25 200910 as 15
test NULL NULL NULL 200910 ag 31

（所影响的行数为 2 行）*/
[/Quote]
UP

[dawugui]

--不过你最好用动态SQL吧。万一你输入的不是两个固定值，需要多个呢？

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 

go

--不过你最好用动态SQL吧。万一你输入的不是两个固定值，需要多个呢？
declare @DateFlag1 as varchar(10)
declare @DateFlag2 as varchar(10)
set @DateFlag1 = '200910'
set @DateFlag2 = '200911'

declare @sql varchar(8000)
set @sql = 'select UserName '
select @sql = @sql + ' , max(case DateFlag when ''' + DateFlag + ''' then DateFlag else null end) [DateFlag_' + DateFlag + ']'
                   + ' , max(case DateFlag when ''' + DateFlag + ''' then Part else null end) [Part_' + DateFlag + ']'
                   + ' , max(case DateFlag when ''' + DateFlag + ''' then LoginSum else null end) [LoginSum_' + DateFlag + ']'
from (select distinct DateFlag from test where DateFlag between @DateFlag1 and @DateFlag2) as a
set @sql = @sql + ' from test group by UserName'
exec(@sql) 

drop table test

/*
UserName             DateFlag_200910 Part_200910 LoginSum_200910 DateFlag_200911 Part_200911 LoginSum_200911 
-------------------- --------------- ----------- --------------- --------------- ----------- --------------- 
admin                200910          as          15              200911          ad          25
test                 200910          ag          31              NULL            NULL        NULL

警告: 聚合或其它 SET 操作消除了空值。

*/

[nianran520]

if object_id('[test]') is not null drop table [test]
go
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 


declare @sql varchar(8000)
select @sql= isnull(@sql+',','')+'max(case DateFlag when '''+DateFlag+''' then DateFlag else null end) as [DateFlag],
max(case DateFlag when '''+DateFlag+''' then Part else null end) as [Part],
max(case DateFlag when '''+DateFlag+''' then LoginSum else null end) as [LoginSum]
'
from (select distinct  DateFlag from test) t

select @sql='select UserName,'+@sql+' from test group by UserName'

exec(@sql)
----------------------------
admin	200909	ae	20	200910	as	15	200911	ad	25
test	200909	af	31	200910	ag	31	NULL	NULL	NULL

[--小F--]

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 

--select * from test
select 
  isnull(a.UserName,b.username) as username,a.DateFlag,a.Part,a.LoginSum,b.DateFlag,b.Part,b.LoginSum
from
  (select * from  test where dateflag='200911')a
full join
  (select * from  test where dateflag='200910')b
on
  a.UserName=b.UserName
drop table test
/*username             DateFlag   Part       LoginSum    DateFlag   Part       LoginSum
-------------------- ---------- ---------- ----------- ---------- ---------- -----------
admin                200911     ad         25          200910     as         15
test                 NULL       NULL       NULL        200910     ag         31

(2 行受影响)*/

[--小F--]

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 

--select * from test
declare @sql varchar(8000)
select
   @sql= isnull(@sql+',','')+'
   max(case DateFlag when '''+DateFlag+''' then DateFlag else null end) as [DateFlag],
   max(case DateFlag when '''+DateFlag+''' then Part else null end) as [Part],
   max(case DateFlag when '''+DateFlag+''' then LoginSum else null end) as [LoginSum]'
from 
  (select distinct  DateFlag from test) t
select @sql='select UserName,'+@sql+' from test group by UserName'
exec(@sql)

drop table test
/*username             DateFlag   Part       LoginSum    DateFlag   Part       LoginSum
-------------------- ---------- ---------- ----------- ---------- ---------- -----------
admin                200911     ad         25          200910     as         15
test                 NULL       NULL       NULL        200910     ag         31

(2 行受影响)*/

[nianran520]

--位置反了改改
declare @sql varchar(8000)
select @sql= isnull(@sql+',','')+'max(case DateFlag when '''+DateFlag+''' then DateFlag else null end) as [DateFlag],
max(case DateFlag when '''+DateFlag+''' then Part else null end) as [Part],
max(case DateFlag when '''+DateFlag+''' then LoginSum else null end) as [LoginSum]
'
from (select distinct top 100 percent DateFlag from test order by DateFlag desc) t

select @sql='select UserName,'+@sql+' from test group by UserName order by min(uid)'

exec(@sql)

[Mr_Nice]

[Quote=引用 3 楼 dawugui 的回复:]
--不过你最好用动态SQL吧。万一你输入的不是两个固定值，需要多个呢？

SQL codecreatetable test
(
Uidintidentity(1,1),
UserNamevarchar(20),
DateFlagvarchar(10),
Partvarchar(10),
LoginSumint
)insertinto testselect'admin','200910','as',15unionallselect'admin','200911','ad',25unionallselect'admin','200909','ae',20unionallselect'test','200909','af',31unionallselect'test','200910','ag',31go--不过你最好用动态SQL吧。万一你输入的不是两个固定值，需要多个呢？declare@DateFlag1asvarchar(10)declare@DateFlag2asvarchar(10)set@DateFlag1='200910'set@DateFlag2='200911'declare@sqlvarchar(8000)set@sql='select UserName'select@sql=@sql+' , max(case DateFlag when'''+ DateFlag+''' then DateFlag else null end) [DateFlag_'+ DateFlag+']'+' , max(case DateFlag when'''+ DateFlag+''' then Part else null end) [Part_'+ DateFlag+']'+' , max(case DateFlag when'''+ DateFlag+''' then LoginSum else null end) [LoginSum_'+ DateFlag+']'from (selectdistinct DateFlagfrom testwhere DateFlagbetween@DateFlag1and@DateFlag2)as aset@sql=@sql+' from test group by UserName'exec(@sql)droptable test/*
UserName DateFlag_200910 Part_200910 LoginSum_200910 DateFlag_200911 Part_200911 LoginSum_200911
-------------------- --------------- ----------- --------------- --------------- ----------- ---------------
admin 200910 as 15 200911 ad 25
test 200910 ag 31 NULL NULL NULL

警告: 聚合或其它 SET 操作消除了空值。*/
[/Quote]

比较喜欢 dawugui的动态语句这个！

[ws_hgo]

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 

select username,
     max(case when DateFlag='200910' then DateFlag else null end) 'DateFlag',
     max(case when DateFlag='200910' then Part else null end) 'Part',
     max(case when DateFlag='200910' then LoginSum else null end) 'LoginSum',
     max(case when DateFlag='200911' then username else null end) 'UserName',
     max(case when DateFlag='200911' then DateFlag else null end) 'DateFlag',
     max(case when DateFlag='200911' then Part else null end) 'Part'
from test group by  username

username             DateFlag   Part       LoginSum    UserName             DateFlag   Part
-------------------- ---------- ---------- ----------- -------------------- ---------- ----------
admin                200910     as         15          admin                200911     ad
test                 200910     ag         31          NULL                 NULL       NULL

[albert_skynet]

如何查询合并LoginSum大于等于20的前两条记录呢？
admin 200911 ad 25 200909 ae 20
test 200910 ag 31 200909 af 31

[albert_skynet]

如何查询合并LoginSum最大的前两条记录呢？
admin 200911 ad 25 200909 ae 20
test 200910 ag 31 200909 af 31

[dawugui]

[Quote=引用 11 楼 albert_skynet 的回复:]
如何查询合并LoginSum最大的前两条记录呢？
admin  200911 ad 25 200909 ae 20
test  200910 ag 31 200909 af 31
[/Quote]
你的测试数据是:

3 admin 200909 ae 20
4 test 200909 af 31
5 test 200910 ag 31