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
(
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 goselect 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 UserNamedrop table test/*
UserName DateFlag Part LoginSum DateFlag Part LoginSum
-------------------- ---------- ---------- ----------- ---------- ---------- -----------
admin 200911 ad 25 200910 as 15
test NULL NULL NULL 200910 ag 31(所影响的行数为 2 行)
*/
(
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 操作消除了空值。*/
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) tselect @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
(
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 行受影响)*/
(
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 行受影响)*/
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) tselect @sql='select UserName,'+@sql+' from test group by UserName order by min(uid)'exec(@sql)
比较喜欢 dawugui的动态语句这个!
(
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 usernameusername DateFlag Part LoginSum UserName DateFlag Part
-------------------- ---------- ---------- ----------- -------------------- ---------- ----------
admin 200910 as 15 admin 200911 ad
test 200910 ag 31 NULL NULL NULL
admin 200911 ad 25 200909 ae 20
test 200910 ag 31 200909 af 31
admin 200911 ad 25 200909 ae 20
test 200910 ag 31 200909 af 31
4 test 200909 af 31
5 test 200910 ag 31