一個類似行列互換的sql語句

 求教一個類似行列互換的sql語句！
我現在有張表cityorder，有如下幾個列：id,amount,city,date。表中有以下一些記錄：
id       amount     city      date
1         12         001       08-11-16
2         14         001       08-11-17
3         25         002       08-11-16
4         23         002       08-11-17
我想求教記錄按如下方式展示的sql語句？？？？
city     08-11-16         08-11-17
001      12                 14
002      25                 23

 

--------------------------------------------------------------------------------------------------------------------------------

 

只取一條記錄的值

SELECT
DISTINCT
city,(SELECT TOP 1 amount FROM cityorder WHERE city=a.city AND CONVERT(CHAR(10),date,121)='2008-11-16') AS '08-11-16',
(SELECT TOP 1 amount FROM cityorder WHERE city=a.city AND CONVERT(CHAR(10),date,121)='2008-11-17') AS '08-11-17'

FROM cityorder a

 

------------------------------------------

 

select
  city,
  (select sum(amount) from cityorder where sdate= to_date('08-11-16','yy-mm-dd')) "08-11-16",
  (select sum(amount) from cityorder where sdate= to_date('08-11-17','yy-mm-dd')) "08-11-17"
from cityorder
group by city;

也許這個對你來說更合適

------------------------------------------

 

oracle有比較方便的行列互換函數

以下是mysql的方式
select x.city,x.amount1,x.amount2 from(select c.city,Group_concat(if(c.date='08-11-16',c.amount,'') separator '') as 08-11-16,Group_concat(if(c.date='08-11-17',c.amount,'') separator '') as 08-11-17 from cityorder c group by c.city) as x;