求教一个sql语句

[zhangfengsummer]

查询语句如下：
select a.BL1,a.DCRQ,b.qhmc from XM_YS_YCXTDC_D a,sys_xzqhb b where a.sxbm in ('410201','410301','410102') and b.qhbm=a.sxbm and DCRQ >= TIMESTAMP '2006-08-01 00:00:00' and DCRQ <= TIMESTAMP '2009-08-09 23:59:59' order by DCRQ,qhmc

结果为以下的格式

BL1 DCRQ QHMC
8.00 2009-06-05 登封县
20.00 2009-06-05 孟县
25.00 2009-06-05 孟津县
14.00 2009-06-10 登封县
22.00 2009-06-10 孟县
35.00 2009-06-10 孟津县
20.00 2009-06-15 登封县
23.00 2009-06-15 孟县
40.00 2009-06-15 孟津县

如何转化成这样的格式

登封县 孟县 孟津县
2009-06-05 8.00 20.00 25.00
2009-06-10 14.00 22.00 35.00
2009-06-15 20.00 23.00 40.00


求教sql语句，不用数据库的特殊关键字，如oracle的rownum等。

[quiettown]

如果县不是固定的还有些个麻烦。。。

SQL> select dcrq , to_char(max(case when qhmc = '登封县' then bl1 else 0 end), '99.99') as "登封县", 
  2      to_char(max(case when qhmc = '孟县  ' then bl1 else 0 end), '99.99') as "孟县", 
  3      to_char(max(case when qhmc = '孟津县' then bl1 else 0 end), '99.99') as "孟津县"
  4    from test
  5   group by dcrq
  6   order by dcrq;

DCRQ                 登封县 孟县   孟津县
-------------------- ------ ------ ------
2009-06-05             8.00  20.00  25.00
2009-06-10            14.00  22.00  35.00
2009-06-15            20.00  23.00  40.00

[ccsongdi]

我个人感觉 楼主应该不是这个意思，虽然这个也实现了！！！

楼主的意思应该 是查询后直接是后一种格式，而不是出来结果后再重新转化成后一种格式吧？？

[cosio]

二楼ＯＫ！

[小灰狼W]

select a.DCRQ,sum(decode(b.qhmc,'登封县',a.bl1)) 登封县,
  sum(decode(b.qhmc,'孟县',a.bl1)) 孟县,
  sum(decode(b.qhmc,'孟津县',a.bl1)) 孟津县 
   from XM_YS_YCXTDC_D a,sys_xzqhb b 
   where a.sxbm in ('410201','410301','410102') and 
     b.qhbm=a.sxbm and DCRQ >= TIMESTAMP '2006-08-01 00:00:00' and 
     DCRQ <= TIMESTAMP '2009-08-09 23:59:59' 
   group by a.dcrq
   order by a.dcrq

[jjaihua]

求和还是求max
顶四楼的

[inthirties]

[Quote=引用 1 楼 quiettown 的回复:]
如果县不是固定的还有些个麻烦。。。
SQL code
SQL>select dcrq , to_char(max(casewhen qhmc='登封县'then bl1else0end),'99.99')as "登封县",2 to_char(max(casewhen qhmc='孟县'then bl1else0end),'99.99')as "孟县",3 to_char(max(casewhen qhmc='孟津县'then bl1else0end),'99.99')as "孟津县"4from test5groupby dcrq6orderby dcrq;

DCRQ 登封县 孟县 孟津县-------------------- ------ ------ ------2009-06-058.0020.0025.002009-06-1014.0022.0035.002009-06-1520.0023.0040.00
[/Quote]

支持这个，这样的竖转横的用decode和group by的配合。