表:
symbol baojia1 baojia2 qixian
1123 12 13 1a
1123 22 23 2a
2123 24 86 1a
2123 24 46 2a
……………………………………希望(高效)得到:
symbol baojia1_1a baojia2_1a baojia1_2a baojia2_2a
1123 12 13 22 23
2123 24 86 24 46
………………………………………………………………
symbol baojia1 baojia2 qixian
1123 12 13 1a
1123 22 23 2a
2123 24 86 1a
2123 24 46 2a
……………………………………希望(高效)得到:
symbol baojia1_1a baojia2_1a baojia1_2a baojia2_2a
1123 12 13 22 23
2123 24 86 24 46
………………………………………………………………
max(case when qixian='1a' then baojia2) as baojia2_1a ,
max(case when qixian='2a' then baojia1) as baojia1_2a ,
max(case when qixian='2a' then baojia1) as baojia2_2a
group by symbol
下面这个
测试————————
create table #tab (symbol varchar(10), baojia1 varchar(10), baojia2 varchar(10), qixian varchar(10))insert into #tab
select '1123', '12' , '13' , '1a'
union all
select '1123' ,'22' , '23' ,'2a'
union all
select '2123' ,'24' , '86' ,'1a'
union all
select '2123 ' , '24' ,'46' ,'2a'——————————
select symbol,max(case when qixian='1a' then baojia1 end) as baojia1_1a ,
max(case when qixian='1a' then baojia2 end) as baojia2_1a ,
max(case when qixian='2a' then baojia1 end) as baojia1_2a ,
max(case when qixian='2a' then baojia2 end) as baojia2_2a
from #tab
group by symbol
——————————
结果——————
symbol baojia1_1a baojia2_1a baojia1_2a baojia2_2a
---------- ---------- ---------- ---------- ----------
1123 12 13 22 23
2123 24 86 24 46
表:
symbol baojia1 baojia2 qixian date
1123 12 13 1a 2007-01-01
1123 22 23 2a 2007-01-01
1123 32 35 1a 2007-02-01
1123 66 67 2a 2007-02-01
2123 24 86 1a 2006-01-01
2123 24 46 2a 2006-01-01
……………………………………希望(高效)得到:
symbol baojia1_1a baojia2_1a baojia1_2a baojia2_2a date
1123 12 13 22 23 2007-01-01
1123 32 35 66 67 2007-02-01
2123 24 86 24 46 2006-01-01
………………………………………………………………
max(case when qixian='1a' then baojia2 end) as baojia2_1a ,
max(case when qixian='2a' then baojia1 end) as baojia1_2a ,
max(case when qixian='2a' then baojia2 end) as baojia2_2a ,
[date]
from #tab
group by symbol,[date] order by symbol,[date]