请教一个SQL语句该如何写

请问一个SQL语句该怎么写
原表数据如下：
ID AMOUT COLUMNNO
8218387275.72 3
82183852721.752
8218436762.13 3
82184327079.472
8218465513.56 3

想要得到的结果：
ID AMOUT-1 AMOUNT-2
821838 7275.72 52721.75
821843 6762.13 27079.47
821846 5513.56

即，原表的AMOUT是由COLUMNNO 来区分的，现在要去掉列COLUMNNO，把由他区分的两列数据合并成一列。

请教各位。
谢谢。


[解决办法]

SQL code

select * from tbpivot (max(AMOUT) for COLUMNNO in([3],[2])) piv
[解决办法]
SQL code
------------------------------ Author  :TravyLee(物是人非事事休，欲语泪先流！)-- Date    :2012-10-16 19:31:12-- Version:--      Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) --    Jul  9 2008 14:43:34 --    Copyright (c) 1988-2008 Microsoft Corporation--    Developer Edition on Windows NT 6.1 <X86> (Build 7600: )--------------------------------> 测试数据：[test]if object_id('[test]') is not null drop table [test]go create table [test]([ID] int,[AMOUT] numeric(7,2),[COLUMNNO] int)insert [test]select 821838,7275.72,3 union allselect 821838,52721.75,2 union allselect 821843,6762.13,3 union allselect 821843,27079.47,2 union allselect 821846,5513.56,3gowith tas(select     px=row_number()over(partition by [ID] order by [COLUMNNO] desc),    *from    test)select    [ID],    SUM(case when px=1 then [AMOUT] else 0 end) as [AMOUT-1],    SUM(case when px=2 then [AMOUT] else 0 end) as [AMOUT-2]from    tgroup by    [ID]----------------结果----------------------------/* ID          AMOUT-1                                 AMOUT-2----------- --------------------------------------- ---------------------------------------821838      7275.72                                 52721.75821843      6762.13                                 27079.47821846      5513.56                                 0.00(3 行受影响)*/
[解决办法]
SQL code
create table [test]( [ID] int, [AMOUT] numeric(7,2), [COLUMNNO] int ) insert [test] select 821838,7275.72,3 union all select 821838,52721.75,2 union all select 821843,6762.13,3 union all select 821843,27079.47,2 union all select 821846,5513.56,3   SELECT id,[AMOUT-1]=MAX(CASE WHEN [COLUMNNO]=3 THEN [AMOUT] ELSE 0 END ),  [AMOUT-1]=MAX(CASE WHEN [COLUMNNO]=2 THEN [AMOUT] ELSE 0 END ) FROM test GROUP BY id /* id          AMOUT-1                                 AMOUT-1 ----------- --------------------------------------- --------------------------------------- 821838      7275.72                                 52721.75 821843      6762.13                                 27079.47 821846      5513.56                                 0.00  (3 行受影响)  */