以下用sql 如何實現
有表A(a,b)
a b
1 a
1 b
2 c
2 d
3 e
3 f
查詢結果
a1 a2 a3
a c e
b d f
如何用sql 查詢得到以上結果？
[解决办法]
create table 表A(a int,b varchar(1))
insert into 表A(a,b)
select 1,'a' union all
select 1,'b' union all
select 2,'c' union all
select 2,'d' union all
select 3,'e' union all
select 3,'f'
select a,b from 表A
/*
a b
----------- ----
1 a
1 b
2 c
2 d
3 e
3 f
(6 row(s) affected)
*/
select [1] 'a1',[2] 'a2',[3] 'a3'
from
(select a,b,
row_number() over(partition by a order by getdate()) 'rn'
from 表A) t
pivot(max(b) for a in([1],[2],[3])) p
/*
a1 a2 a3
---- ---- ----
a c e
b d f
(2 row(s) affected)
*/
create table tA(a int,b varchar(1))
insert into tA(a,b)
select 1,'a'
union all
select 1,'b'
union all
select 2,'c'
union all
select 2,'d'
union all
select 3,'e'
union all
select 3,'f'
select max(case when a=1 then b end) a1,max(case when a=2 then b end) a2,max(case when a=3 then b end) a3 from
(select a,b,row_number() over(partition by a order by getdate()) rn from ta) p
group by rn
