请教一下,我有一张表,字段有:id、name、flag, id可重复,同一个id指向两个name,两个name各属于不同的两个类,用flag区分,我如何做查询出相同id的数据列?例如: id name flag
11 aa x
11 bb y
12 cc x
12 dd y
13 ee x
13 ff y查询结果: id name name
11 aa bb
12 cc dd
13 ee ff
11 aa x
11 bb y
12 cc x
12 dd y
13 ee x
13 ff y查询结果: id name name
11 aa bb
12 cc dd
13 ee ff
if object_id('tempdb.dbo.#') is not null drop table #
create table #(id int, name varchar(8), flag varchar(8))
insert into #
select 11, 'aa', 'x' union all
select 11, 'bb', 'y' union all
select 12, 'cc', 'x' union all
select 12, 'dd', 'y' union all
select 13, 'ee', 'x' union all
select 13, 'ff', 'y'select a.id, a.name, b.name from # a join # b on a.id=b.id and a.flag='x' and b.flag='y'/*
id name name
----------- -------- --------
11 aa bb
12 cc dd
13 ee ff
*/
insert into tb values(11, 'aa', 'x')
insert into tb values(11, 'bb', 'y')
insert into tb values(12, 'cc', 'x')
insert into tb values(12, 'dd', 'y')
insert into tb values(13, 'ee', 'x')
insert into tb values(13, 'ff', 'y')
goselect id ,min(name) name1 , max(name) name2 from tb group by id
/*
id name1 name2
----------- ---------- ----------
11 aa bb
12 cc dd
13 ee ff(所影响的行数为 3 行)
*/select id ,
max(case flag when 'X' then name else '' end) name_x,
max(case flag when 'Y' then name else '' end) name_y
from tb
group by id
/*
id name_x name_y
----------- ---------- ----------
11 aa bb
12 cc dd
13 ee ff(所影响的行数为 3 行)
*/
drop table tb
--1
select id,
case when level=1 then name else '' end as name_x,
case when level=2 then name else '' end as name_y
from
(select *,row_number() over(partiton by id order by name asc) as level from tb) a
--