我有一张桌子,看起来像7 Y/ A* R- {$ u& [$ ~- I9 {0 O
Test Reader Result John 1.6 Jack 5.22 Ursula 2.53 Albert 3. Jack 5.1而且我知道每个测试最多可以有3个,Readers所以我想创造一个看起来像看起来像一个的 * c* O/ |* u7 a& {4 CTest Reader_1 Result_1 Reader_2 Result_2 Reader_3 Result_31 John 1.6 Jack 5.二 (null) (null)二 Ursula 2.5 Jack 5. (null) (null)3 Albert 3. (null) (null) (null) (null)环顾四周,我似乎需要使用它PIVOT表。唯一的问题是,我找到了使用序数变量创建列的例子。在这种情况下,我有可能列(3)来定义数量。你有什么想法吗? , p$ b; v- j$ y3 z9 c( W8 ? 3 k3 W# F( S2 W/ z( u 解决方案: " J# s( h6 ]! ~9 i f 不需要多个自联解决方案:( u2 N3 Y" i$ n, M
您可以PIVOT根据ROW_NUMBER()分析函数的结果获得所需的输出: $ R9 R1 a4 r* u. ?( `- PSELECT *FROM ( SELECT t.*, ROW_NUMBER() OVER ( PARTITION BY test ORDER BY ROWNUM ) AS rn FROM your_table t)PIVOT ( MAX( Reader ) AS reader,MAX( result ) AS result FOR rn IN ( 1,2,3 ) ;输出 :0 J1 r$ p1 F# t
TEST 1_READER 1_RESULT 2_READER 2_RESULT 3_READER 3_RESULT---- -------- -------- -------- -------- -------- -------- 1 John .6 Jack 555555 5555555555555555 555555555555 .2 2 Ursula 2.5 Jack 555555 5555555555555555 555555555555 .1 3 Albert 3.0或者只使用聚合函数: , F0 u1 R" v) D4 R9 F6 KSELECT test, MAX( CASE rn WHEN 1 THEN reader END ) AS reader_ MAX( CASE rn WHEN 1 THEN result END ) AS result_ MAX( CASE rn WHEN 2 THEN reader END ) AS reader_二、 MAX( CASE rn WHEN 2 THEN result END ) AS result_二、 MAX( CASE rn WHEN 3 THEN reader END ) AS reader_3、 MAX( CASE rn WHEN 3 THEN result END ) AS result_3FROM ( SELECT t.*, ROW_NUMBER() OVER ( PARTITION BY test ORDER BY ROWNUM ) AS rn FROM your_table t)GROUP BY test;