有表如下:
id username birthday
1 陈晓东 1987-11-03
2 陈慧琳 1989-12-03
要转成如下:
user1 birthday1 user2 birthday2
陈晓东 1987-11-03 陈慧琳 1989-12-03
id username birthday
1 陈晓东 1987-11-03
2 陈慧琳 1989-12-03
要转成如下:
user1 birthday1 user2 birthday2
陈晓东 1987-11-03 陈慧琳 1989-12-03
insert into tb values(1 , '陈晓东' , '1987-11-03')
insert into tb values(2 , '陈慧琳' , '1989-12-03')
godeclare @sql varchar(8000)
set @sql = 'select '
select @sql = @sql + ' max(case id when ''' + cast(id as varchar) + ''' then username end) [user' + cast(id as varchar) + '] , '
+ ' max(case id when ''' + cast(id as varchar) + ''' then birthday end) [birthday' + cast(id as varchar) + '] , '
from (select distinct id from tb) as a
set @sql = left(@sql , len(@sql) - 2) + ' ' + ' from tb '
exec(@sql) drop table tb /*
user1 birthday1 user2 birthday2
---------- ------------------------------------------------------ ---------- ------------------------------------------------------
陈晓东 1987-11-03 00:00:00.000 陈慧琳 1989-12-03 00:00:00.000
*/
有表如下:
id username birthday groupby
1 陈晓东 1987-11-03 F2150563-E53A-11DC-86B2-00142A312764
2 陈慧琳 1989-12-03 F2150563-E53A-11DC-86B2-00142A312764
1 张三 1989-12-22 61B48D10-AA38-4BC2-BEA1-00043659F8EE要转成如下:
user1 birthday1 user2 birthday2
陈晓东 1987-11-03 陈慧琳 1989-12-03
张三 1989-12-22 null null
set @sql = 'select '
select @sql = @sql + ' max(case px when ''' + cast(px as varchar) + ''' then 分数 else 0 end) [user' + cast(px as varchar) + '] , '
+ ' max(case px when ''' + cast(px as varchar) + ''' then 分数 else 0 end) [birthday' + cast(px as varchar) + '] , '
from (select distinct px from (select * , px = (select count(1) from tb where id = t.id and username <t.username)+1 from tb t) m) as a
set @sql = left(@sql , len(@sql) - 2) + ' from (select * , px = (select count(1) from tb where id = t.id and username <t.username)+1 from tb t) t group by id'
exec(@sql)
insert into tb values(1 , '陈晓东' , '1987-11-03')
insert into tb values(2 , '陈慧琳' , '1989-12-03')
insert into tb values(1 , '张三' , '1989-12-22')
goselect
max(case id when 1 then username end) user1,
max(case id when 1 then birthday end) birthday1,
max(case id when 2 then username end) user2,
max(case id when 2 then birthday end) birthday2
from
(
select * , px = (select count(1) from tb where id = t.id and username <t.username)+1 from tb t
) m
group by pxdrop table tb /*
user1 birthday1 user2 birthday2
---------- ------------------------------------------------------ ---------- ------------------------------------------------------
陈晓东 1987-11-03 00:00:00.000 陈慧琳 1989-12-03 00:00:00.000
张三 1989-12-22 00:00:00.000 NULL NULL(所影响的行数为 2 行)*/
insert into tb values(1 , '陈晓东' , '1987-11-03')
insert into tb values(2 , '陈慧琳' , '1989-12-03')
insert into tb values(1 , '张三' , '1989-12-22')
go--同ID按照姓名大小排序
select
max(case id when 1 then username end) user1,
max(case id when 1 then birthday end) birthday1,
max(case id when 2 then username end) user2,
max(case id when 2 then birthday end) birthday2
from
(
select * , px = (select count(1) from tb where id = t.id and username <t.username)+1 from tb t
) m
group by px
/*
user1 birthday1 user2 birthday2
---------- ------------------------------------------------------ ---------- ------------------------------------------------------
陈晓东 1987-11-03 00:00:00.000 陈慧琳 1989-12-03 00:00:00.000
张三 1989-12-22 00:00:00.000 NULL NULL(所影响的行数为 2 行)*/
--同ID按照时间大小排序
select
max(case id when 1 then username end) user1,
max(case id when 1 then birthday end) birthday1,
max(case id when 2 then username end) user2,
max(case id when 2 then birthday end) birthday2
from
(
select * , px = (select count(1) from tb where id = t.id and birthday <t.birthday)+1 from tb t
) m
group by px
/*
user1 birthday1 user2 birthday2
---------- ------------------------------------------------------ ---------- ------------------------------------------------------
陈晓东 1987-11-03 00:00:00.000 陈慧琳 1989-12-03 00:00:00.000
张三 1989-12-22 00:00:00.000 NULL NULL(所影响的行数为 2 行)*/
drop table tb