--建立测试环境
Create Table 表(flag varchar(10),name varchar(10),num varchar(10),stri varchar(10))
--插入数据
insert into 表
select '1','a','100','111' union
select '1','b','200','222' union
select '2','a','300','333' union
select '2','b','400','444'
select * from 表
--测试语句
select name, min(case when flag=1 then num end) [num1],
min(case when flag=2 then stri end) [stri1],
min(case when flag=1 then num end) [num2],
min(case when flag=2 then stri end) [stri2]
from 表
group by name
--删除测试环境
Drop Table 表
Create Table 表(flag varchar(10),name varchar(10),num varchar(10),stri varchar(10))
--插入数据
insert into 表
select '1','a','100','111' union
select '1','b','200','222' union
select '2','a','300','333' union
select '2','b','400','444'
select * from 表
--测试语句
select name, min(case when flag=1 then num end) [num1],
min(case when flag=2 then stri end) [stri1],
min(case when flag=1 then num end) [num2],
min(case when flag=2 then stri end) [stri2]
from 表
group by name
--删除测试环境
Drop Table 表
(select * from tb1 where flag=1) a left join
(select * from tb1 where flag=2) b
on a.name=b.name