一个类似行列互换的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;