| >> |
| >>create table t023t1 ( |
| +> a int not null , |
| +> b int not null , |
| +> c int not null , |
| +> d int not null , |
| +> primary key(a) ); |
| |
| --- SQL operation complete. |
| >> |
| >>create table t023t2 ( |
| +> a int not null , |
| +> b int not null , |
| +> c int not null , |
| +> d int not null , |
| +> primary key(a) ); |
| |
| --- SQL operation complete. |
| >> |
| >>create table t023t3 ( |
| +> a int not null , |
| +> b int not null , |
| +> c int not null , |
| +> d int not null , |
| +> primary key(a) ); |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >>create table t023t4 ( |
| +> sk int not null , |
| +> numRows int not null , |
| +> a0 int not null , |
| +> a1 int not null , |
| +> a2 int not null , |
| +> a3 int not null , |
| +> primary key(sk) ); |
| |
| --- SQL operation complete. |
| >> |
| >>create table t023t5 ( |
| +> sk int not null , |
| +> numRows int not null , |
| +> b0 int not null , |
| +> b1 int not null , |
| +> b2 int not null , |
| +> b3 int not null , |
| +> b4 int not null , |
| +> b5 int not null , |
| +> b6 int not null , |
| +> b7 int not null , |
| +> primary key(sk) ); |
| |
| --- SQL operation complete. |
| >> |
| >>create table t023t6 ( |
| +> sk int not null , |
| +> numRows int not null , |
| +> c0 int not null , |
| +> c1 int not null , |
| +> c2 int not null , |
| +> c3 int not null , |
| +> c4 int not null , |
| +> c5 int not null , |
| +> c6 int not null , |
| +> c7 int not null , |
| +> primary key(sk) ); |
| |
| --- SQL operation complete. |
| >> |
| >>create table t023t7 ( |
| +> sk int not null , |
| +> numRows int not null , |
| +> d0 int not null , |
| +> d1 int not null , |
| +> d2 int not null , |
| +> d3 int not null , |
| +> d4 int not null , |
| +> d5 int not null , |
| +> d6 int not null , |
| +> d7 int not null , |
| +> d8 int not null , |
| +> d9 int not null , |
| +> d10 int not null , |
| +> d11 int not null , |
| +> d12 int not null , |
| +> d13 int not null , |
| +> d14 int not null , |
| +> d15 int not null , |
| +> primary key(sk) ); |
| |
| --- SQL operation complete. |
| >> |
| >>?section dml |
| >> |
| >>-- POPULATE TABLES |
| >> |
| >>insert into t023t1 values |
| +> (0, 0, 0, 0), |
| +> (1, 10, 100, 1000), |
| +> (2, 20, 200, 2000), |
| +> (3, 30, 300, 3000), |
| +> (4, 40, 400, 4000), |
| +> (5, 50, 500, 5000), |
| +> (6, 60, 600, 6000), |
| +> (7, 70, 700, 7000), |
| +> (8, 80, 800, 8000), |
| +> (9, 90, 900, 9000); |
| |
| --- 10 row(s) inserted. |
| >> |
| >>insert into t023t2 ( |
| +> select a * 10 + c1, c1, c1 * 10, c1 * 100 |
| +> from t023t1 |
| +> transpose 0,1,2,3,4,5,6,7,8,9 as c1 |
| +> key by k1); |
| |
| --- 100 row(s) inserted. |
| >> |
| >>insert into t023t3 ( |
| +> select a * 10 + c1, c1, c + c1, d + (c1 * 10) |
| +> from t023t2 |
| +> transpose 0,1,2,3,4,5,6,7,8,9 as c1 |
| +> key by k1); |
| |
| --- 1000 row(s) inserted. |
| >> |
| >> |
| >>insert into t023t4 values (0, 4, 0, 1, 2, 3), |
| +> (4, 4, 4, 5, 6, 7), |
| +> (8, 4, 8, 9, 10, 11), |
| +> (12, 4, 12, 13, 14, 15), |
| +> (16, 4, 16, 17, 18, 19), |
| +> (20, 4, 20, 21, 22, 23), |
| +> (24, 4, 24, 25, 26, 27), |
| +> (28, 4, 28, 29, 30, 31), |
| +> (32, 4, 32, 33, 34, 35); |
| |
| --- 9 row(s) inserted. |
| >> |
| >>insert into t023t5 values |
| +> (0, 8, 100, 101, 102, 103, 104, 105, 106, 107), |
| +> (8, 8, 108, 109, 110, 111, 112, 113, 114, 115), |
| +> (16, 8, 116, 117, 118, 119, 120, 121, 122, 123), |
| +> (24, 8, 124, 125, 126, 127, 128, 129, 130, 131), |
| +> (32, 4, 132, 133, 134, 135, -1, -1, -1, -1); |
| |
| --- 5 row(s) inserted. |
| >> |
| >>insert into t023t6 values |
| +> (0, 8, 200, 201, 202, 203, 204, 205, 206, 207), |
| +> (8, 8, 208, 209, 210, 211, 212, 213, 214, 215), |
| +> (16, 8, 216, 217, 218, 219, 220, 221, 222, 223), |
| +> (24, 8, 224, 225, 226, 227, 228, 229, 230, 231), |
| +> (32, 4, 232, 233, 234, 235, -1, -1, -1, -1); |
| |
| --- 5 row(s) inserted. |
| >> |
| >>insert into t023t7 values |
| +> (0, 16, 300, 301, 302, 303, 304, 305, 306, 307, |
| +> 308, 309, 310, 311, 312, 313, 314, 315), |
| +> (16, 16, 316, 317, 318, 319, 320, 321, 322, 323, |
| +> 324, 325, 326, 327, 328, 329, 330, 331), |
| +> (32, 4, 332, 333, 334, 335, -1, -1, -1, -1, |
| +> -1,-1,-1,-1,-1,-1,-1,-1); |
| |
| --- 3 row(s) inserted. |
| >> |
| >>-- SOME BASIC QUERIES USING TRANSPOSE. |
| >> |
| >> select * |
| +> from t023t1 |
| +>transpose a,b,c,d as c1 |
| +> key by k1; |
| |
| A B C D K1 C1 |
| ----------- ----------- ----------- ----------- ----------- ----------- |
| |
| 0 0 0 0 1 0 |
| 0 0 0 0 2 0 |
| 0 0 0 0 3 0 |
| 0 0 0 0 4 0 |
| 1 10 100 1000 1 1 |
| 1 10 100 1000 2 10 |
| 1 10 100 1000 3 100 |
| 1 10 100 1000 4 1000 |
| 2 20 200 2000 1 2 |
| 2 20 200 2000 2 20 |
| 2 20 200 2000 3 200 |
| 2 20 200 2000 4 2000 |
| 3 30 300 3000 1 3 |
| 3 30 300 3000 2 30 |
| 3 30 300 3000 3 300 |
| 3 30 300 3000 4 3000 |
| 4 40 400 4000 1 4 |
| 4 40 400 4000 2 40 |
| 4 40 400 4000 3 400 |
| 4 40 400 4000 4 4000 |
| 5 50 500 5000 1 5 |
| 5 50 500 5000 2 50 |
| 5 50 500 5000 3 500 |
| 5 50 500 5000 4 5000 |
| 6 60 600 6000 1 6 |
| 6 60 600 6000 2 60 |
| 6 60 600 6000 3 600 |
| 6 60 600 6000 4 6000 |
| 7 70 700 7000 1 7 |
| 7 70 700 7000 2 70 |
| 7 70 700 7000 3 700 |
| 7 70 700 7000 4 7000 |
| 8 80 800 8000 1 8 |
| 8 80 800 8000 2 80 |
| 8 80 800 8000 3 800 |
| 8 80 800 8000 4 8000 |
| 9 90 900 9000 1 9 |
| 9 90 900 9000 2 90 |
| 9 90 900 9000 3 900 |
| 9 90 900 9000 4 9000 |
| |
| --- 40 row(s) selected. |
| >> |
| >> select * |
| +> from t023t1 |
| +>transpose a+b,b+c,c+d,d+a as c1 |
| +> key by k1; |
| |
| A B C D K1 C1 |
| ----------- ----------- ----------- ----------- ----------- -------------------- |
| |
| 0 0 0 0 1 0 |
| 0 0 0 0 2 0 |
| 0 0 0 0 3 0 |
| 0 0 0 0 4 0 |
| 1 10 100 1000 1 11 |
| 1 10 100 1000 2 110 |
| 1 10 100 1000 3 1100 |
| 1 10 100 1000 4 1001 |
| 2 20 200 2000 1 22 |
| 2 20 200 2000 2 220 |
| 2 20 200 2000 3 2200 |
| 2 20 200 2000 4 2002 |
| 3 30 300 3000 1 33 |
| 3 30 300 3000 2 330 |
| 3 30 300 3000 3 3300 |
| 3 30 300 3000 4 3003 |
| 4 40 400 4000 1 44 |
| 4 40 400 4000 2 440 |
| 4 40 400 4000 3 4400 |
| 4 40 400 4000 4 4004 |
| 5 50 500 5000 1 55 |
| 5 50 500 5000 2 550 |
| 5 50 500 5000 3 5500 |
| 5 50 500 5000 4 5005 |
| 6 60 600 6000 1 66 |
| 6 60 600 6000 2 660 |
| 6 60 600 6000 3 6600 |
| 6 60 600 6000 4 6006 |
| 7 70 700 7000 1 77 |
| 7 70 700 7000 2 770 |
| 7 70 700 7000 3 7700 |
| 7 70 700 7000 4 7007 |
| 8 80 800 8000 1 88 |
| 8 80 800 8000 2 880 |
| 8 80 800 8000 3 8800 |
| 8 80 800 8000 4 8008 |
| 9 90 900 9000 1 99 |
| 9 90 900 9000 2 990 |
| 9 90 900 9000 3 9900 |
| 9 90 900 9000 4 9009 |
| |
| --- 40 row(s) selected. |
| >> |
| >> select * |
| +> from t023t1 |
| +>transpose a,b,c,d as c1 |
| +> key by k1 |
| +>transpose c1, k1 as c2 |
| +> key by k2; |
| |
| A B C D K1 C1 K2 C2 |
| ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- |
| |
| 0 0 0 0 1 0 1 0 |
| 0 0 0 0 1 0 2 1 |
| 0 0 0 0 2 0 1 0 |
| 0 0 0 0 2 0 2 2 |
| 0 0 0 0 3 0 1 0 |
| 0 0 0 0 3 0 2 3 |
| 0 0 0 0 4 0 1 0 |
| 0 0 0 0 4 0 2 4 |
| 1 10 100 1000 1 1 1 1 |
| 1 10 100 1000 1 1 2 1 |
| 1 10 100 1000 2 10 1 10 |
| 1 10 100 1000 2 10 2 2 |
| 1 10 100 1000 3 100 1 100 |
| 1 10 100 1000 3 100 2 3 |
| 1 10 100 1000 4 1000 1 1000 |
| 1 10 100 1000 4 1000 2 4 |
| 2 20 200 2000 1 2 1 2 |
| 2 20 200 2000 1 2 2 1 |
| 2 20 200 2000 2 20 1 20 |
| 2 20 200 2000 2 20 2 2 |
| 2 20 200 2000 3 200 1 200 |
| 2 20 200 2000 3 200 2 3 |
| 2 20 200 2000 4 2000 1 2000 |
| 2 20 200 2000 4 2000 2 4 |
| 3 30 300 3000 1 3 1 3 |
| 3 30 300 3000 1 3 2 1 |
| 3 30 300 3000 2 30 1 30 |
| 3 30 300 3000 2 30 2 2 |
| 3 30 300 3000 3 300 1 300 |
| 3 30 300 3000 3 300 2 3 |
| 3 30 300 3000 4 3000 1 3000 |
| 3 30 300 3000 4 3000 2 4 |
| 4 40 400 4000 1 4 1 4 |
| 4 40 400 4000 1 4 2 1 |
| 4 40 400 4000 2 40 1 40 |
| 4 40 400 4000 2 40 2 2 |
| 4 40 400 4000 3 400 1 400 |
| 4 40 400 4000 3 400 2 3 |
| 4 40 400 4000 4 4000 1 4000 |
| 4 40 400 4000 4 4000 2 4 |
| 5 50 500 5000 1 5 1 5 |
| 5 50 500 5000 1 5 2 1 |
| 5 50 500 5000 2 50 1 50 |
| 5 50 500 5000 2 50 2 2 |
| 5 50 500 5000 3 500 1 500 |
| 5 50 500 5000 3 500 2 3 |
| 5 50 500 5000 4 5000 1 5000 |
| 5 50 500 5000 4 5000 2 4 |
| 6 60 600 6000 1 6 1 6 |
| 6 60 600 6000 1 6 2 1 |
| 6 60 600 6000 2 60 1 60 |
| 6 60 600 6000 2 60 2 2 |
| 6 60 600 6000 3 600 1 600 |
| 6 60 600 6000 3 600 2 3 |
| 6 60 600 6000 4 6000 1 6000 |
| 6 60 600 6000 4 6000 2 4 |
| 7 70 700 7000 1 7 1 7 |
| 7 70 700 7000 1 7 2 1 |
| 7 70 700 7000 2 70 1 70 |
| 7 70 700 7000 2 70 2 2 |
| 7 70 700 7000 3 700 1 700 |
| 7 70 700 7000 3 700 2 3 |
| 7 70 700 7000 4 7000 1 7000 |
| 7 70 700 7000 4 7000 2 4 |
| 8 80 800 8000 1 8 1 8 |
| 8 80 800 8000 1 8 2 1 |
| 8 80 800 8000 2 80 1 80 |
| 8 80 800 8000 2 80 2 2 |
| 8 80 800 8000 3 800 1 800 |
| 8 80 800 8000 3 800 2 3 |
| 8 80 800 8000 4 8000 1 8000 |
| 8 80 800 8000 4 8000 2 4 |
| 9 90 900 9000 1 9 1 9 |
| 9 90 900 9000 1 9 2 1 |
| 9 90 900 9000 2 90 1 90 |
| 9 90 900 9000 2 90 2 2 |
| 9 90 900 9000 3 900 1 900 |
| 9 90 900 9000 3 900 2 3 |
| 9 90 900 9000 4 9000 1 9000 |
| 9 90 900 9000 4 9000 2 4 |
| |
| --- 80 row(s) selected. |
| >> |
| >> select * |
| +> from t023t1 |
| +>transpose a,b,c,d as c1 |
| +> 1 + a, 2 + b, 3 + c, 4 + d as c2 |
| +> key by k1; |
| |
| A B C D K1 C1 C2 |
| ----------- ----------- ----------- ----------- ----------- ----------- -------------------- |
| |
| 0 0 0 0 1 0 ? |
| 0 0 0 0 2 0 ? |
| 0 0 0 0 3 0 ? |
| 0 0 0 0 4 0 ? |
| 0 0 0 0 5 ? 1 |
| 0 0 0 0 6 ? 2 |
| 0 0 0 0 7 ? 3 |
| 0 0 0 0 8 ? 4 |
| 1 10 100 1000 1 1 ? |
| 1 10 100 1000 2 10 ? |
| 1 10 100 1000 3 100 ? |
| 1 10 100 1000 4 1000 ? |
| 1 10 100 1000 5 ? 2 |
| 1 10 100 1000 6 ? 12 |
| 1 10 100 1000 7 ? 103 |
| 1 10 100 1000 8 ? 1004 |
| 2 20 200 2000 1 2 ? |
| 2 20 200 2000 2 20 ? |
| 2 20 200 2000 3 200 ? |
| 2 20 200 2000 4 2000 ? |
| 2 20 200 2000 5 ? 3 |
| 2 20 200 2000 6 ? 22 |
| 2 20 200 2000 7 ? 203 |
| 2 20 200 2000 8 ? 2004 |
| 3 30 300 3000 1 3 ? |
| 3 30 300 3000 2 30 ? |
| 3 30 300 3000 3 300 ? |
| 3 30 300 3000 4 3000 ? |
| 3 30 300 3000 5 ? 4 |
| 3 30 300 3000 6 ? 32 |
| 3 30 300 3000 7 ? 303 |
| 3 30 300 3000 8 ? 3004 |
| 4 40 400 4000 1 4 ? |
| 4 40 400 4000 2 40 ? |
| 4 40 400 4000 3 400 ? |
| 4 40 400 4000 4 4000 ? |
| 4 40 400 4000 5 ? 5 |
| 4 40 400 4000 6 ? 42 |
| 4 40 400 4000 7 ? 403 |
| 4 40 400 4000 8 ? 4004 |
| 5 50 500 5000 1 5 ? |
| 5 50 500 5000 2 50 ? |
| 5 50 500 5000 3 500 ? |
| 5 50 500 5000 4 5000 ? |
| 5 50 500 5000 5 ? 6 |
| 5 50 500 5000 6 ? 52 |
| 5 50 500 5000 7 ? 503 |
| 5 50 500 5000 8 ? 5004 |
| 6 60 600 6000 1 6 ? |
| 6 60 600 6000 2 60 ? |
| 6 60 600 6000 3 600 ? |
| 6 60 600 6000 4 6000 ? |
| 6 60 600 6000 5 ? 7 |
| 6 60 600 6000 6 ? 62 |
| 6 60 600 6000 7 ? 603 |
| 6 60 600 6000 8 ? 6004 |
| 7 70 700 7000 1 7 ? |
| 7 70 700 7000 2 70 ? |
| 7 70 700 7000 3 700 ? |
| 7 70 700 7000 4 7000 ? |
| 7 70 700 7000 5 ? 8 |
| 7 70 700 7000 6 ? 72 |
| 7 70 700 7000 7 ? 703 |
| 7 70 700 7000 8 ? 7004 |
| 8 80 800 8000 1 8 ? |
| 8 80 800 8000 2 80 ? |
| 8 80 800 8000 3 800 ? |
| 8 80 800 8000 4 8000 ? |
| 8 80 800 8000 5 ? 9 |
| 8 80 800 8000 6 ? 82 |
| 8 80 800 8000 7 ? 803 |
| 8 80 800 8000 8 ? 8004 |
| 9 90 900 9000 1 9 ? |
| 9 90 900 9000 2 90 ? |
| 9 90 900 9000 3 900 ? |
| 9 90 900 9000 4 9000 ? |
| 9 90 900 9000 5 ? 10 |
| 9 90 900 9000 6 ? 92 |
| 9 90 900 9000 7 ? 903 |
| 9 90 900 9000 8 ? 9004 |
| |
| --- 80 row(s) selected. |
| >> |
| >> |
| >>-- All combinations of legal clauses with transpose. |
| >>-- |
| >>-- from_clause |
| >>-- [where_clause] |
| >>-- [transpose_clause]+ |
| >>-- [groupby_clause] |
| >>-- [having_clause] |
| >>-- |
| >> |
| >> select * |
| +> from t023t1 |
| +>transpose a, b, c, d as c1 |
| +> 1, 2, 3, 4 as c2 |
| +> key by k1 |
| +>transpose d, c, b, a as c3 |
| +> key by k2; |
| |
| A B C D K1 C1 C2 K2 C3 |
| ----------- ----------- ----------- ----------- ----------- ----------- ------ ----------- ----------- |
| |
| 0 0 0 0 1 0 ? 1 0 |
| 0 0 0 0 1 0 ? 2 0 |
| 0 0 0 0 1 0 ? 3 0 |
| 0 0 0 0 1 0 ? 4 0 |
| 0 0 0 0 2 0 ? 1 0 |
| 0 0 0 0 2 0 ? 2 0 |
| 0 0 0 0 2 0 ? 3 0 |
| 0 0 0 0 2 0 ? 4 0 |
| 0 0 0 0 3 0 ? 1 0 |
| 0 0 0 0 3 0 ? 2 0 |
| 0 0 0 0 3 0 ? 3 0 |
| 0 0 0 0 3 0 ? 4 0 |
| 0 0 0 0 4 0 ? 1 0 |
| 0 0 0 0 4 0 ? 2 0 |
| 0 0 0 0 4 0 ? 3 0 |
| 0 0 0 0 4 0 ? 4 0 |
| 0 0 0 0 5 ? 1 1 0 |
| 0 0 0 0 5 ? 1 2 0 |
| 0 0 0 0 5 ? 1 3 0 |
| 0 0 0 0 5 ? 1 4 0 |
| 0 0 0 0 6 ? 2 1 0 |
| 0 0 0 0 6 ? 2 2 0 |
| 0 0 0 0 6 ? 2 3 0 |
| 0 0 0 0 6 ? 2 4 0 |
| 0 0 0 0 7 ? 3 1 0 |
| 0 0 0 0 7 ? 3 2 0 |
| 0 0 0 0 7 ? 3 3 0 |
| 0 0 0 0 7 ? 3 4 0 |
| 0 0 0 0 8 ? 4 1 0 |
| 0 0 0 0 8 ? 4 2 0 |
| 0 0 0 0 8 ? 4 3 0 |
| 0 0 0 0 8 ? 4 4 0 |
| 1 10 100 1000 1 1 ? 1 1000 |
| 1 10 100 1000 1 1 ? 2 100 |
| 1 10 100 1000 1 1 ? 3 10 |
| 1 10 100 1000 1 1 ? 4 1 |
| 1 10 100 1000 2 10 ? 1 1000 |
| 1 10 100 1000 2 10 ? 2 100 |
| 1 10 100 1000 2 10 ? 3 10 |
| 1 10 100 1000 2 10 ? 4 1 |
| 1 10 100 1000 3 100 ? 1 1000 |
| 1 10 100 1000 3 100 ? 2 100 |
| 1 10 100 1000 3 100 ? 3 10 |
| 1 10 100 1000 3 100 ? 4 1 |
| 1 10 100 1000 4 1000 ? 1 1000 |
| 1 10 100 1000 4 1000 ? 2 100 |
| 1 10 100 1000 4 1000 ? 3 10 |
| 1 10 100 1000 4 1000 ? 4 1 |
| 1 10 100 1000 5 ? 1 1 1000 |
| 1 10 100 1000 5 ? 1 2 100 |
| 1 10 100 1000 5 ? 1 3 10 |
| 1 10 100 1000 5 ? 1 4 1 |
| 1 10 100 1000 6 ? 2 1 1000 |
| 1 10 100 1000 6 ? 2 2 100 |
| 1 10 100 1000 6 ? 2 3 10 |
| 1 10 100 1000 6 ? 2 4 1 |
| 1 10 100 1000 7 ? 3 1 1000 |
| 1 10 100 1000 7 ? 3 2 100 |
| 1 10 100 1000 7 ? 3 3 10 |
| 1 10 100 1000 7 ? 3 4 1 |
| 1 10 100 1000 8 ? 4 1 1000 |
| 1 10 100 1000 8 ? 4 2 100 |
| 1 10 100 1000 8 ? 4 3 10 |
| 1 10 100 1000 8 ? 4 4 1 |
| 2 20 200 2000 1 2 ? 1 2000 |
| 2 20 200 2000 1 2 ? 2 200 |
| 2 20 200 2000 1 2 ? 3 20 |
| 2 20 200 2000 1 2 ? 4 2 |
| 2 20 200 2000 2 20 ? 1 2000 |
| 2 20 200 2000 2 20 ? 2 200 |
| 2 20 200 2000 2 20 ? 3 20 |
| 2 20 200 2000 2 20 ? 4 2 |
| 2 20 200 2000 3 200 ? 1 2000 |
| 2 20 200 2000 3 200 ? 2 200 |
| 2 20 200 2000 3 200 ? 3 20 |
| 2 20 200 2000 3 200 ? 4 2 |
| 2 20 200 2000 4 2000 ? 1 2000 |
| 2 20 200 2000 4 2000 ? 2 200 |
| 2 20 200 2000 4 2000 ? 3 20 |
| 2 20 200 2000 4 2000 ? 4 2 |
| 2 20 200 2000 5 ? 1 1 2000 |
| 2 20 200 2000 5 ? 1 2 200 |
| 2 20 200 2000 5 ? 1 3 20 |
| 2 20 200 2000 5 ? 1 4 2 |
| 2 20 200 2000 6 ? 2 1 2000 |
| 2 20 200 2000 6 ? 2 2 200 |
| 2 20 200 2000 6 ? 2 3 20 |
| 2 20 200 2000 6 ? 2 4 2 |
| 2 20 200 2000 7 ? 3 1 2000 |
| 2 20 200 2000 7 ? 3 2 200 |
| 2 20 200 2000 7 ? 3 3 20 |
| 2 20 200 2000 7 ? 3 4 2 |
| 2 20 200 2000 8 ? 4 1 2000 |
| 2 20 200 2000 8 ? 4 2 200 |
| 2 20 200 2000 8 ? 4 3 20 |
| 2 20 200 2000 8 ? 4 4 2 |
| 3 30 300 3000 1 3 ? 1 3000 |
| 3 30 300 3000 1 3 ? 2 300 |
| 3 30 300 3000 1 3 ? 3 30 |
| 3 30 300 3000 1 3 ? 4 3 |
| 3 30 300 3000 2 30 ? 1 3000 |
| 3 30 300 3000 2 30 ? 2 300 |
| 3 30 300 3000 2 30 ? 3 30 |
| 3 30 300 3000 2 30 ? 4 3 |
| 3 30 300 3000 3 300 ? 1 3000 |
| 3 30 300 3000 3 300 ? 2 300 |
| 3 30 300 3000 3 300 ? 3 30 |
| 3 30 300 3000 3 300 ? 4 3 |
| 3 30 300 3000 4 3000 ? 1 3000 |
| 3 30 300 3000 4 3000 ? 2 300 |
| 3 30 300 3000 4 3000 ? 3 30 |
| 3 30 300 3000 4 3000 ? 4 3 |
| 3 30 300 3000 5 ? 1 1 3000 |
| 3 30 300 3000 5 ? 1 2 300 |
| 3 30 300 3000 5 ? 1 3 30 |
| 3 30 300 3000 5 ? 1 4 3 |
| 3 30 300 3000 6 ? 2 1 3000 |
| 3 30 300 3000 6 ? 2 2 300 |
| 3 30 300 3000 6 ? 2 3 30 |
| 3 30 300 3000 6 ? 2 4 3 |
| 3 30 300 3000 7 ? 3 1 3000 |
| 3 30 300 3000 7 ? 3 2 300 |
| 3 30 300 3000 7 ? 3 3 30 |
| 3 30 300 3000 7 ? 3 4 3 |
| 3 30 300 3000 8 ? 4 1 3000 |
| 3 30 300 3000 8 ? 4 2 300 |
| 3 30 300 3000 8 ? 4 3 30 |
| 3 30 300 3000 8 ? 4 4 3 |
| 4 40 400 4000 1 4 ? 1 4000 |
| 4 40 400 4000 1 4 ? 2 400 |
| 4 40 400 4000 1 4 ? 3 40 |
| 4 40 400 4000 1 4 ? 4 4 |
| 4 40 400 4000 2 40 ? 1 4000 |
| 4 40 400 4000 2 40 ? 2 400 |
| 4 40 400 4000 2 40 ? 3 40 |
| 4 40 400 4000 2 40 ? 4 4 |
| 4 40 400 4000 3 400 ? 1 4000 |
| 4 40 400 4000 3 400 ? 2 400 |
| 4 40 400 4000 3 400 ? 3 40 |
| 4 40 400 4000 3 400 ? 4 4 |
| 4 40 400 4000 4 4000 ? 1 4000 |
| 4 40 400 4000 4 4000 ? 2 400 |
| 4 40 400 4000 4 4000 ? 3 40 |
| 4 40 400 4000 4 4000 ? 4 4 |
| 4 40 400 4000 5 ? 1 1 4000 |
| 4 40 400 4000 5 ? 1 2 400 |
| 4 40 400 4000 5 ? 1 3 40 |
| 4 40 400 4000 5 ? 1 4 4 |
| 4 40 400 4000 6 ? 2 1 4000 |
| 4 40 400 4000 6 ? 2 2 400 |
| 4 40 400 4000 6 ? 2 3 40 |
| 4 40 400 4000 6 ? 2 4 4 |
| 4 40 400 4000 7 ? 3 1 4000 |
| 4 40 400 4000 7 ? 3 2 400 |
| 4 40 400 4000 7 ? 3 3 40 |
| 4 40 400 4000 7 ? 3 4 4 |
| 4 40 400 4000 8 ? 4 1 4000 |
| 4 40 400 4000 8 ? 4 2 400 |
| 4 40 400 4000 8 ? 4 3 40 |
| 4 40 400 4000 8 ? 4 4 4 |
| 5 50 500 5000 1 5 ? 1 5000 |
| 5 50 500 5000 1 5 ? 2 500 |
| 5 50 500 5000 1 5 ? 3 50 |
| 5 50 500 5000 1 5 ? 4 5 |
| 5 50 500 5000 2 50 ? 1 5000 |
| 5 50 500 5000 2 50 ? 2 500 |
| 5 50 500 5000 2 50 ? 3 50 |
| 5 50 500 5000 2 50 ? 4 5 |
| 5 50 500 5000 3 500 ? 1 5000 |
| 5 50 500 5000 3 500 ? 2 500 |
| 5 50 500 5000 3 500 ? 3 50 |
| 5 50 500 5000 3 500 ? 4 5 |
| 5 50 500 5000 4 5000 ? 1 5000 |
| 5 50 500 5000 4 5000 ? 2 500 |
| 5 50 500 5000 4 5000 ? 3 50 |
| 5 50 500 5000 4 5000 ? 4 5 |
| 5 50 500 5000 5 ? 1 1 5000 |
| 5 50 500 5000 5 ? 1 2 500 |
| 5 50 500 5000 5 ? 1 3 50 |
| 5 50 500 5000 5 ? 1 4 5 |
| 5 50 500 5000 6 ? 2 1 5000 |
| 5 50 500 5000 6 ? 2 2 500 |
| 5 50 500 5000 6 ? 2 3 50 |
| 5 50 500 5000 6 ? 2 4 5 |
| 5 50 500 5000 7 ? 3 1 5000 |
| 5 50 500 5000 7 ? 3 2 500 |
| 5 50 500 5000 7 ? 3 3 50 |
| 5 50 500 5000 7 ? 3 4 5 |
| 5 50 500 5000 8 ? 4 1 5000 |
| 5 50 500 5000 8 ? 4 2 500 |
| 5 50 500 5000 8 ? 4 3 50 |
| 5 50 500 5000 8 ? 4 4 5 |
| 6 60 600 6000 1 6 ? 1 6000 |
| 6 60 600 6000 1 6 ? 2 600 |
| 6 60 600 6000 1 6 ? 3 60 |
| 6 60 600 6000 1 6 ? 4 6 |
| 6 60 600 6000 2 60 ? 1 6000 |
| 6 60 600 6000 2 60 ? 2 600 |
| 6 60 600 6000 2 60 ? 3 60 |
| 6 60 600 6000 2 60 ? 4 6 |
| 6 60 600 6000 3 600 ? 1 6000 |
| 6 60 600 6000 3 600 ? 2 600 |
| 6 60 600 6000 3 600 ? 3 60 |
| 6 60 600 6000 3 600 ? 4 6 |
| 6 60 600 6000 4 6000 ? 1 6000 |
| 6 60 600 6000 4 6000 ? 2 600 |
| 6 60 600 6000 4 6000 ? 3 60 |
| 6 60 600 6000 4 6000 ? 4 6 |
| 6 60 600 6000 5 ? 1 1 6000 |
| 6 60 600 6000 5 ? 1 2 600 |
| 6 60 600 6000 5 ? 1 3 60 |
| 6 60 600 6000 5 ? 1 4 6 |
| 6 60 600 6000 6 ? 2 1 6000 |
| 6 60 600 6000 6 ? 2 2 600 |
| 6 60 600 6000 6 ? 2 3 60 |
| 6 60 600 6000 6 ? 2 4 6 |
| 6 60 600 6000 7 ? 3 1 6000 |
| 6 60 600 6000 7 ? 3 2 600 |
| 6 60 600 6000 7 ? 3 3 60 |
| 6 60 600 6000 7 ? 3 4 6 |
| 6 60 600 6000 8 ? 4 1 6000 |
| 6 60 600 6000 8 ? 4 2 600 |
| 6 60 600 6000 8 ? 4 3 60 |
| 6 60 600 6000 8 ? 4 4 6 |
| 7 70 700 7000 1 7 ? 1 7000 |
| 7 70 700 7000 1 7 ? 2 700 |
| 7 70 700 7000 1 7 ? 3 70 |
| 7 70 700 7000 1 7 ? 4 7 |
| 7 70 700 7000 2 70 ? 1 7000 |
| 7 70 700 7000 2 70 ? 2 700 |
| 7 70 700 7000 2 70 ? 3 70 |
| 7 70 700 7000 2 70 ? 4 7 |
| 7 70 700 7000 3 700 ? 1 7000 |
| 7 70 700 7000 3 700 ? 2 700 |
| 7 70 700 7000 3 700 ? 3 70 |
| 7 70 700 7000 3 700 ? 4 7 |
| 7 70 700 7000 4 7000 ? 1 7000 |
| 7 70 700 7000 4 7000 ? 2 700 |
| 7 70 700 7000 4 7000 ? 3 70 |
| 7 70 700 7000 4 7000 ? 4 7 |
| 7 70 700 7000 5 ? 1 1 7000 |
| 7 70 700 7000 5 ? 1 2 700 |
| 7 70 700 7000 5 ? 1 3 70 |
| 7 70 700 7000 5 ? 1 4 7 |
| 7 70 700 7000 6 ? 2 1 7000 |
| 7 70 700 7000 6 ? 2 2 700 |
| 7 70 700 7000 6 ? 2 3 70 |
| 7 70 700 7000 6 ? 2 4 7 |
| 7 70 700 7000 7 ? 3 1 7000 |
| 7 70 700 7000 7 ? 3 2 700 |
| 7 70 700 7000 7 ? 3 3 70 |
| 7 70 700 7000 7 ? 3 4 7 |
| 7 70 700 7000 8 ? 4 1 7000 |
| 7 70 700 7000 8 ? 4 2 700 |
| 7 70 700 7000 8 ? 4 3 70 |
| 7 70 700 7000 8 ? 4 4 7 |
| 8 80 800 8000 1 8 ? 1 8000 |
| 8 80 800 8000 1 8 ? 2 800 |
| 8 80 800 8000 1 8 ? 3 80 |
| 8 80 800 8000 1 8 ? 4 8 |
| 8 80 800 8000 2 80 ? 1 8000 |
| 8 80 800 8000 2 80 ? 2 800 |
| 8 80 800 8000 2 80 ? 3 80 |
| 8 80 800 8000 2 80 ? 4 8 |
| 8 80 800 8000 3 800 ? 1 8000 |
| 8 80 800 8000 3 800 ? 2 800 |
| 8 80 800 8000 3 800 ? 3 80 |
| 8 80 800 8000 3 800 ? 4 8 |
| 8 80 800 8000 4 8000 ? 1 8000 |
| 8 80 800 8000 4 8000 ? 2 800 |
| 8 80 800 8000 4 8000 ? 3 80 |
| 8 80 800 8000 4 8000 ? 4 8 |
| 8 80 800 8000 5 ? 1 1 8000 |
| 8 80 800 8000 5 ? 1 2 800 |
| 8 80 800 8000 5 ? 1 3 80 |
| 8 80 800 8000 5 ? 1 4 8 |
| 8 80 800 8000 6 ? 2 1 8000 |
| 8 80 800 8000 6 ? 2 2 800 |
| 8 80 800 8000 6 ? 2 3 80 |
| 8 80 800 8000 6 ? 2 4 8 |
| 8 80 800 8000 7 ? 3 1 8000 |
| 8 80 800 8000 7 ? 3 2 800 |
| 8 80 800 8000 7 ? 3 3 80 |
| 8 80 800 8000 7 ? 3 4 8 |
| 8 80 800 8000 8 ? 4 1 8000 |
| 8 80 800 8000 8 ? 4 2 800 |
| 8 80 800 8000 8 ? 4 3 80 |
| 8 80 800 8000 8 ? 4 4 8 |
| 9 90 900 9000 1 9 ? 1 9000 |
| 9 90 900 9000 1 9 ? 2 900 |
| 9 90 900 9000 1 9 ? 3 90 |
| 9 90 900 9000 1 9 ? 4 9 |
| 9 90 900 9000 2 90 ? 1 9000 |
| 9 90 900 9000 2 90 ? 2 900 |
| 9 90 900 9000 2 90 ? 3 90 |
| 9 90 900 9000 2 90 ? 4 9 |
| 9 90 900 9000 3 900 ? 1 9000 |
| 9 90 900 9000 3 900 ? 2 900 |
| 9 90 900 9000 3 900 ? 3 90 |
| 9 90 900 9000 3 900 ? 4 9 |
| 9 90 900 9000 4 9000 ? 1 9000 |
| 9 90 900 9000 4 9000 ? 2 900 |
| 9 90 900 9000 4 9000 ? 3 90 |
| 9 90 900 9000 4 9000 ? 4 9 |
| 9 90 900 9000 5 ? 1 1 9000 |
| 9 90 900 9000 5 ? 1 2 900 |
| 9 90 900 9000 5 ? 1 3 90 |
| 9 90 900 9000 5 ? 1 4 9 |
| 9 90 900 9000 6 ? 2 1 9000 |
| 9 90 900 9000 6 ? 2 2 900 |
| 9 90 900 9000 6 ? 2 3 90 |
| 9 90 900 9000 6 ? 2 4 9 |
| 9 90 900 9000 7 ? 3 1 9000 |
| 9 90 900 9000 7 ? 3 2 900 |
| 9 90 900 9000 7 ? 3 3 90 |
| 9 90 900 9000 7 ? 3 4 9 |
| 9 90 900 9000 8 ? 4 1 9000 |
| 9 90 900 9000 8 ? 4 2 900 |
| 9 90 900 9000 8 ? 4 3 90 |
| 9 90 900 9000 8 ? 4 4 9 |
| |
| --- 320 row(s) selected. |
| >> |
| >> select sum(a) |
| +> from t023t1 |
| +>transpose a, b, c, d as c1 |
| +> 1, 2, 3, 4 as c2 |
| +> key by k1 |
| +>transpose d, c, b, a as c3 |
| +> key by k2 |
| +> having sum(a) > 10; |
| |
| (EXPR) |
| -------------------- |
| |
| 1440 |
| |
| --- 1 row(s) selected. |
| >> |
| >> select sum(a) |
| +> from t023t1 |
| +>transpose a, b, c, d as c1 |
| +> 1, 2, 3, 4 as c2 |
| +> key by k1 |
| +>transpose d, c, b, a as c3 |
| +> key by k2 |
| +> group by k1, k2; |
| |
| (EXPR) |
| -------------------- |
| |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| |
| --- 32 row(s) selected. |
| >> |
| >> select sum(a) |
| +> from t023t1 |
| +>transpose a, b, c, d as c1 |
| +> 1, 2, 3, 4 as c2 |
| +> key by k1 |
| +>transpose d, c, b, a as c3 |
| +> key by k2 |
| +> group by k1, k2 |
| +> having sum(a) > 10; |
| |
| (EXPR) |
| -------------------- |
| |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| 45 |
| |
| --- 32 row(s) selected. |
| >> |
| >> select * |
| +> from t023t1 |
| +> where a > 5 |
| +> and b < 80 |
| +>transpose a, b, c, d as c1 |
| +> 1, 2, 3, 4 as c2 |
| +> key by k1 |
| +>transpose d, c, b, a as c3 |
| +> key by k2; |
| |
| A B C D K1 C1 C2 K2 C3 |
| ----------- ----------- ----------- ----------- ----------- ----------- ------ ----------- ----------- |
| |
| 6 60 600 6000 1 6 ? 1 6000 |
| 6 60 600 6000 1 6 ? 2 600 |
| 6 60 600 6000 1 6 ? 3 60 |
| 6 60 600 6000 1 6 ? 4 6 |
| 6 60 600 6000 2 60 ? 1 6000 |
| 6 60 600 6000 2 60 ? 2 600 |
| 6 60 600 6000 2 60 ? 3 60 |
| 6 60 600 6000 2 60 ? 4 6 |
| 6 60 600 6000 3 600 ? 1 6000 |
| 6 60 600 6000 3 600 ? 2 600 |
| 6 60 600 6000 3 600 ? 3 60 |
| 6 60 600 6000 3 600 ? 4 6 |
| 6 60 600 6000 4 6000 ? 1 6000 |
| 6 60 600 6000 4 6000 ? 2 600 |
| 6 60 600 6000 4 6000 ? 3 60 |
| 6 60 600 6000 4 6000 ? 4 6 |
| 6 60 600 6000 5 ? 1 1 6000 |
| 6 60 600 6000 5 ? 1 2 600 |
| 6 60 600 6000 5 ? 1 3 60 |
| 6 60 600 6000 5 ? 1 4 6 |
| 6 60 600 6000 6 ? 2 1 6000 |
| 6 60 600 6000 6 ? 2 2 600 |
| 6 60 600 6000 6 ? 2 3 60 |
| 6 60 600 6000 6 ? 2 4 6 |
| 6 60 600 6000 7 ? 3 1 6000 |
| 6 60 600 6000 7 ? 3 2 600 |
| 6 60 600 6000 7 ? 3 3 60 |
| 6 60 600 6000 7 ? 3 4 6 |
| 6 60 600 6000 8 ? 4 1 6000 |
| 6 60 600 6000 8 ? 4 2 600 |
| 6 60 600 6000 8 ? 4 3 60 |
| 6 60 600 6000 8 ? 4 4 6 |
| 7 70 700 7000 1 7 ? 1 7000 |
| 7 70 700 7000 1 7 ? 2 700 |
| 7 70 700 7000 1 7 ? 3 70 |
| 7 70 700 7000 1 7 ? 4 7 |
| 7 70 700 7000 2 70 ? 1 7000 |
| 7 70 700 7000 2 70 ? 2 700 |
| 7 70 700 7000 2 70 ? 3 70 |
| 7 70 700 7000 2 70 ? 4 7 |
| 7 70 700 7000 3 700 ? 1 7000 |
| 7 70 700 7000 3 700 ? 2 700 |
| 7 70 700 7000 3 700 ? 3 70 |
| 7 70 700 7000 3 700 ? 4 7 |
| 7 70 700 7000 4 7000 ? 1 7000 |
| 7 70 700 7000 4 7000 ? 2 700 |
| 7 70 700 7000 4 7000 ? 3 70 |
| 7 70 700 7000 4 7000 ? 4 7 |
| 7 70 700 7000 5 ? 1 1 7000 |
| 7 70 700 7000 5 ? 1 2 700 |
| 7 70 700 7000 5 ? 1 3 70 |
| 7 70 700 7000 5 ? 1 4 7 |
| 7 70 700 7000 6 ? 2 1 7000 |
| 7 70 700 7000 6 ? 2 2 700 |
| 7 70 700 7000 6 ? 2 3 70 |
| 7 70 700 7000 6 ? 2 4 7 |
| 7 70 700 7000 7 ? 3 1 7000 |
| 7 70 700 7000 7 ? 3 2 700 |
| 7 70 700 7000 7 ? 3 3 70 |
| 7 70 700 7000 7 ? 3 4 7 |
| 7 70 700 7000 8 ? 4 1 7000 |
| 7 70 700 7000 8 ? 4 2 700 |
| 7 70 700 7000 8 ? 4 3 70 |
| 7 70 700 7000 8 ? 4 4 7 |
| |
| --- 64 row(s) selected. |
| >> |
| >> select sum(a) |
| +> from t023t1 |
| +> where a > 5 |
| +> and b < 80 |
| +>transpose a, b, c, d as c1 |
| +> 1, 2, 3, 4 as c2 |
| +> key by k1 |
| +>transpose d, c, b, a as c3 |
| +> key by k2 |
| +> having sum(a) > 10; |
| |
| (EXPR) |
| -------------------- |
| |
| 416 |
| |
| --- 1 row(s) selected. |
| >> |
| >> select sum(a) |
| +> from t023t1 |
| +> where a > 5 |
| +> and b < 80 |
| +>transpose a, b, c, d as c1 |
| +> 1, 2, 3, 4 as c2 |
| +> key by k1 |
| +>transpose d, c, b, a as c3 |
| +> key by k2 |
| +> group by k1, k2; |
| |
| (EXPR) |
| -------------------- |
| |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| |
| --- 32 row(s) selected. |
| >> |
| >> select sum(a) |
| +> from t023t1 |
| +> where a > 5 |
| +> and b < 80 |
| +>transpose a, b, c, d as c1 |
| +> 1, 2, 3, 4 as c2 |
| +> key by k1 |
| +>transpose d, c, b, a as c3 |
| +> key by k2 |
| +> group by k1, k2 |
| +> having sum(a) > 10; |
| |
| (EXPR) |
| -------------------- |
| |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| 13 |
| |
| --- 32 row(s) selected. |
| >> |
| >> |
| >>-- Subqueries within the Transpose expressions. |
| >> |
| >> select * |
| +> from t023t1 |
| +>transpose a,b,c,d as c1 |
| +> (select sum(a) from t023t2), |
| +> (select sum(b) from t023t2), |
| +> (select sum(c) from t023t2), |
| +> (select sum(d) from t023t2) as c2 |
| +> key by k1; |
| |
| A B C D K1 C1 C2 |
| ----------- ----------- ----------- ----------- ----------- ----------- -------------------- |
| |
| 0 0 0 0 1 0 ? |
| 0 0 0 0 2 0 ? |
| 0 0 0 0 3 0 ? |
| 0 0 0 0 4 0 ? |
| 0 0 0 0 5 ? 4950 |
| 0 0 0 0 6 ? 450 |
| 0 0 0 0 7 ? 4500 |
| 0 0 0 0 8 ? 45000 |
| 9 90 900 9000 1 9 ? |
| 9 90 900 9000 2 90 ? |
| 9 90 900 9000 3 900 ? |
| 9 90 900 9000 4 9000 ? |
| 9 90 900 9000 5 ? 4950 |
| 9 90 900 9000 6 ? 450 |
| 9 90 900 9000 7 ? 4500 |
| 9 90 900 9000 8 ? 45000 |
| 8 80 800 8000 1 8 ? |
| 8 80 800 8000 2 80 ? |
| 8 80 800 8000 3 800 ? |
| 8 80 800 8000 4 8000 ? |
| 8 80 800 8000 5 ? 4950 |
| 8 80 800 8000 6 ? 450 |
| 8 80 800 8000 7 ? 4500 |
| 8 80 800 8000 8 ? 45000 |
| 7 70 700 7000 1 7 ? |
| 7 70 700 7000 2 70 ? |
| 7 70 700 7000 3 700 ? |
| 7 70 700 7000 4 7000 ? |
| 7 70 700 7000 5 ? 4950 |
| 7 70 700 7000 6 ? 450 |
| 7 70 700 7000 7 ? 4500 |
| 7 70 700 7000 8 ? 45000 |
| 6 60 600 6000 1 6 ? |
| 6 60 600 6000 2 60 ? |
| 6 60 600 6000 3 600 ? |
| 6 60 600 6000 4 6000 ? |
| 6 60 600 6000 5 ? 4950 |
| 6 60 600 6000 6 ? 450 |
| 6 60 600 6000 7 ? 4500 |
| 6 60 600 6000 8 ? 45000 |
| 5 50 500 5000 1 5 ? |
| 5 50 500 5000 2 50 ? |
| 5 50 500 5000 3 500 ? |
| 5 50 500 5000 4 5000 ? |
| 5 50 500 5000 5 ? 4950 |
| 5 50 500 5000 6 ? 450 |
| 5 50 500 5000 7 ? 4500 |
| 5 50 500 5000 8 ? 45000 |
| 4 40 400 4000 1 4 ? |
| 4 40 400 4000 2 40 ? |
| 4 40 400 4000 3 400 ? |
| 4 40 400 4000 4 4000 ? |
| 4 40 400 4000 5 ? 4950 |
| 4 40 400 4000 6 ? 450 |
| 4 40 400 4000 7 ? 4500 |
| 4 40 400 4000 8 ? 45000 |
| 3 30 300 3000 1 3 ? |
| 3 30 300 3000 2 30 ? |
| 3 30 300 3000 3 300 ? |
| 3 30 300 3000 4 3000 ? |
| 3 30 300 3000 5 ? 4950 |
| 3 30 300 3000 6 ? 450 |
| 3 30 300 3000 7 ? 4500 |
| 3 30 300 3000 8 ? 45000 |
| 2 20 200 2000 1 2 ? |
| 2 20 200 2000 2 20 ? |
| 2 20 200 2000 3 200 ? |
| 2 20 200 2000 4 2000 ? |
| 2 20 200 2000 5 ? 4950 |
| 2 20 200 2000 6 ? 450 |
| 2 20 200 2000 7 ? 4500 |
| 2 20 200 2000 8 ? 45000 |
| 1 10 100 1000 1 1 ? |
| 1 10 100 1000 2 10 ? |
| 1 10 100 1000 3 100 ? |
| 1 10 100 1000 4 1000 ? |
| 1 10 100 1000 5 ? 4950 |
| 1 10 100 1000 6 ? 450 |
| 1 10 100 1000 7 ? 4500 |
| 1 10 100 1000 8 ? 45000 |
| |
| --- 80 row(s) selected. |
| >> |
| >>-- Queries typical of DataMining application |
| >>-- Calculate multiple crosstables, trunctating the IV's |
| >>-- |
| >> select ctid, iv, b, count(*) |
| +> from t023t3 |
| +>transpose Cast(a/100 as int), |
| +> c - (Cast(c/10 as int) * 10), |
| +> d - (Cast(d/3 as int) * 3) as iv |
| +> key by ctid |
| +> group by ctid, iv, b; |
| |
| CTID IV B (EXPR) |
| ----------- -------------------- ----------- -------------------- |
| |
| 2 1 1 100 |
| 3 1 1 40 |
| 2 3 3 100 |
| 3 1 4 40 |
| 2 5 5 100 |
| 2 7 7 100 |
| 3 1 7 40 |
| 2 9 9 100 |
| 3 1 0 30 |
| 3 1 3 30 |
| 3 1 6 30 |
| 3 1 9 30 |
| 3 1 2 30 |
| 3 1 5 30 |
| 3 1 8 30 |
| 1 1 0 10 |
| 1 1 1 10 |
| 1 1 2 10 |
| 1 1 3 10 |
| 1 1 4 10 |
| 1 1 5 10 |
| 1 1 6 10 |
| 1 1 7 10 |
| 1 1 8 10 |
| 1 1 9 10 |
| 1 3 0 10 |
| 1 3 1 10 |
| 1 3 2 10 |
| 1 3 3 10 |
| 1 3 4 10 |
| 1 3 5 10 |
| 1 3 6 10 |
| 1 3 7 10 |
| 1 3 8 10 |
| 1 3 9 10 |
| 1 5 0 10 |
| 1 5 1 10 |
| 1 5 2 10 |
| 1 5 3 10 |
| 1 5 4 10 |
| 1 5 5 10 |
| 1 5 6 10 |
| 1 5 7 10 |
| 1 5 8 10 |
| 1 5 9 10 |
| 1 7 0 10 |
| 1 7 1 10 |
| 1 7 2 10 |
| 1 7 3 10 |
| 1 7 4 10 |
| 1 7 5 10 |
| 1 7 6 10 |
| 1 7 7 10 |
| 1 7 8 10 |
| 1 7 9 10 |
| 1 9 0 10 |
| 1 9 1 10 |
| 1 9 2 10 |
| 1 9 3 10 |
| 1 9 4 10 |
| 1 9 5 10 |
| 1 9 6 10 |
| 1 9 7 10 |
| 1 9 8 10 |
| 1 9 9 10 |
| 1 0 0 10 |
| 2 0 0 100 |
| 3 0 0 40 |
| 1 0 1 10 |
| 1 0 2 10 |
| 2 2 2 100 |
| 3 2 2 40 |
| 1 0 3 10 |
| 3 0 3 40 |
| 1 0 4 10 |
| 2 4 4 100 |
| 1 0 5 10 |
| 3 2 5 40 |
| 1 0 6 10 |
| 2 6 6 100 |
| 3 0 6 40 |
| 1 0 7 10 |
| 1 0 8 10 |
| 2 8 8 100 |
| 3 2 8 40 |
| 1 0 9 10 |
| 3 0 9 40 |
| 3 2 1 30 |
| 3 0 2 30 |
| 3 2 4 30 |
| 3 0 5 30 |
| 3 2 7 30 |
| 3 0 8 30 |
| 3 2 0 30 |
| 3 0 1 30 |
| 3 2 3 30 |
| 3 0 4 30 |
| 3 2 6 30 |
| 3 0 7 30 |
| 3 2 9 30 |
| 1 2 0 10 |
| 1 2 1 10 |
| 1 2 2 10 |
| 1 2 3 10 |
| 1 2 4 10 |
| 1 2 5 10 |
| 1 2 6 10 |
| 1 2 7 10 |
| 1 2 8 10 |
| 1 2 9 10 |
| 1 4 0 10 |
| 1 4 1 10 |
| 1 4 2 10 |
| 1 4 3 10 |
| 1 4 4 10 |
| 1 4 5 10 |
| 1 4 6 10 |
| 1 4 7 10 |
| 1 4 8 10 |
| 1 4 9 10 |
| 1 6 0 10 |
| 1 6 1 10 |
| 1 6 2 10 |
| 1 6 3 10 |
| 1 6 4 10 |
| 1 6 5 10 |
| 1 6 6 10 |
| 1 6 7 10 |
| 1 6 8 10 |
| 1 6 9 10 |
| 1 8 0 10 |
| 1 8 1 10 |
| 1 8 2 10 |
| 1 8 3 10 |
| 1 8 4 10 |
| 1 8 5 10 |
| 1 8 6 10 |
| 1 8 7 10 |
| 1 8 8 10 |
| 1 8 9 10 |
| |
| --- 140 row(s) selected. |
| >> |
| >>-- Calculate each crosstable independently. |
| >> |
| >> select ctid, iv, b, count(*) |
| +> from t023t3 |
| +>transpose Cast(a/100 as int) as iv |
| +> key by ctid |
| +> group by ctid, iv, b; |
| |
| CTID IV B (EXPR) |
| ----------- ----------- ----------- -------------------- |
| |
| 1 1 0 10 |
| 1 1 1 10 |
| 1 1 2 10 |
| 1 1 3 10 |
| 1 1 4 10 |
| 1 1 5 10 |
| 1 1 6 10 |
| 1 1 7 10 |
| 1 1 8 10 |
| 1 1 9 10 |
| 1 3 0 10 |
| 1 3 1 10 |
| 1 3 2 10 |
| 1 3 3 10 |
| 1 3 4 10 |
| 1 3 5 10 |
| 1 3 6 10 |
| 1 3 7 10 |
| 1 3 8 10 |
| 1 3 9 10 |
| 1 5 0 10 |
| 1 5 1 10 |
| 1 5 2 10 |
| 1 5 3 10 |
| 1 5 4 10 |
| 1 5 5 10 |
| 1 5 6 10 |
| 1 5 7 10 |
| 1 5 8 10 |
| 1 5 9 10 |
| 1 7 0 10 |
| 1 7 1 10 |
| 1 7 2 10 |
| 1 7 3 10 |
| 1 7 4 10 |
| 1 7 5 10 |
| 1 7 6 10 |
| 1 7 7 10 |
| 1 7 8 10 |
| 1 7 9 10 |
| 1 9 0 10 |
| 1 9 1 10 |
| 1 9 2 10 |
| 1 9 3 10 |
| 1 9 4 10 |
| 1 9 5 10 |
| 1 9 6 10 |
| 1 9 7 10 |
| 1 9 8 10 |
| 1 9 9 10 |
| 1 0 0 10 |
| 1 0 1 10 |
| 1 0 2 10 |
| 1 0 3 10 |
| 1 0 4 10 |
| 1 0 5 10 |
| 1 0 6 10 |
| 1 0 7 10 |
| 1 0 8 10 |
| 1 0 9 10 |
| 1 2 0 10 |
| 1 2 1 10 |
| 1 2 2 10 |
| 1 2 3 10 |
| 1 2 4 10 |
| 1 2 5 10 |
| 1 2 6 10 |
| 1 2 7 10 |
| 1 2 8 10 |
| 1 2 9 10 |
| 1 4 0 10 |
| 1 4 1 10 |
| 1 4 2 10 |
| 1 4 3 10 |
| 1 4 4 10 |
| 1 4 5 10 |
| 1 4 6 10 |
| 1 4 7 10 |
| 1 4 8 10 |
| 1 4 9 10 |
| 1 6 0 10 |
| 1 6 1 10 |
| 1 6 2 10 |
| 1 6 3 10 |
| 1 6 4 10 |
| 1 6 5 10 |
| 1 6 6 10 |
| 1 6 7 10 |
| 1 6 8 10 |
| 1 6 9 10 |
| 1 8 0 10 |
| 1 8 1 10 |
| 1 8 2 10 |
| 1 8 3 10 |
| 1 8 4 10 |
| 1 8 5 10 |
| 1 8 6 10 |
| 1 8 7 10 |
| 1 8 8 10 |
| 1 8 9 10 |
| |
| --- 100 row(s) selected. |
| >> |
| >> select ctid, iv, b, count(*) |
| +> from t023t3 |
| +>transpose c - (Cast(c/10 as int) * 10) as iv |
| +> key by ctid |
| +> group by ctid, iv, b; |
| |
| CTID IV B (EXPR) |
| ----------- -------------------- ----------- -------------------- |
| |
| 1 1 1 100 |
| 1 3 3 100 |
| 1 5 5 100 |
| 1 7 7 100 |
| 1 9 9 100 |
| 1 0 0 100 |
| 1 2 2 100 |
| 1 4 4 100 |
| 1 6 6 100 |
| 1 8 8 100 |
| |
| --- 10 row(s) selected. |
| >> |
| >> select ctid, iv, b, count(*) |
| +> from t023t3 |
| +>transpose d - (Cast(d/3 as int) * 3) as iv |
| +> key by ctid |
| +> group by ctid, iv, b; |
| |
| CTID IV B (EXPR) |
| ----------- -------------------- ----------- -------------------- |
| |
| 1 1 1 40 |
| 1 1 4 40 |
| 1 1 7 40 |
| 1 1 0 30 |
| 1 1 3 30 |
| 1 1 6 30 |
| 1 1 9 30 |
| 1 1 2 30 |
| 1 1 5 30 |
| 1 1 8 30 |
| 1 0 0 40 |
| 1 2 2 40 |
| 1 0 3 40 |
| 1 2 5 40 |
| 1 0 6 40 |
| 1 2 8 40 |
| 1 0 9 40 |
| 1 2 1 30 |
| 1 0 2 30 |
| 1 2 4 30 |
| 1 0 5 30 |
| 1 2 7 30 |
| 1 0 8 30 |
| 1 2 0 30 |
| 1 0 1 30 |
| 1 2 3 30 |
| 1 0 4 30 |
| 1 2 6 30 |
| 1 0 7 30 |
| 1 2 9 30 |
| |
| --- 30 row(s) selected. |
| >> |
| >> |
| >>-- Query typical of a continuous crosstable. |
| >> |
| >> select ctid, b, Cast(count(*) as int), |
| +> Cast(avg(iv) as int), |
| +> Cast(variance(iv) as int), |
| +> Cast(stddev(iv) as int) |
| +> from t023t3 |
| +>transpose a, c, b as iv |
| +> key by ctid |
| +> group by ctid, b |
| +> order by ctid, b; |
| |
| CTID B (EXPR) (EXPR) (EXPR) (EXPR) |
| ----------- ----------- ----------- ----------- ----------- ----------- |
| |
| 1 0 100 495 84166 290 |
| 1 1 100 496 84166 290 |
| 1 2 100 497 84166 290 |
| 1 3 100 498 84166 290 |
| 1 4 100 499 84166 290 |
| 1 5 100 500 84166 290 |
| 1 6 100 501 84166 290 |
| 1 7 100 502 84166 290 |
| 1 8 100 503 84166 290 |
| 1 9 100 504 84166 290 |
| 2 0 100 45 833 28 |
| 2 1 100 46 833 28 |
| 2 2 100 47 833 28 |
| 2 3 100 48 833 28 |
| 2 4 100 49 833 28 |
| 2 5 100 50 833 28 |
| 2 6 100 51 833 28 |
| 2 7 100 52 833 28 |
| 2 8 100 53 833 28 |
| 2 9 100 54 833 28 |
| 3 0 100 0 0 0 |
| 3 1 100 1 0 0 |
| 3 2 100 2 0 0 |
| 3 3 100 3 0 0 |
| 3 4 100 4 0 0 |
| 3 5 100 5 0 0 |
| 3 6 100 6 0 0 |
| 3 7 100 7 0 0 |
| 3 8 100 8 0 0 |
| 3 9 100 9 0 0 |
| |
| --- 30 row(s) selected. |
| >> |
| >> select ctid, b, count(*) |
| +> from t023t3 |
| +>transpose a, c, b as iv |
| +> key by ctid |
| +> group by ctid, b |
| +> order by ctid, b; |
| |
| CTID B (EXPR) |
| ----------- ----------- -------------------- |
| |
| 1 0 100 |
| 1 1 100 |
| 1 2 100 |
| 1 3 100 |
| 1 4 100 |
| 1 5 100 |
| 1 6 100 |
| 1 7 100 |
| 1 8 100 |
| 1 9 100 |
| 2 0 100 |
| 2 1 100 |
| 2 2 100 |
| 2 3 100 |
| 2 4 100 |
| 2 5 100 |
| 2 6 100 |
| 2 7 100 |
| 2 8 100 |
| 2 9 100 |
| 3 0 100 |
| 3 1 100 |
| 3 2 100 |
| 3 3 100 |
| 3 4 100 |
| 3 5 100 |
| 3 6 100 |
| 3 7 100 |
| 3 8 100 |
| 3 9 100 |
| |
| --- 30 row(s) selected. |
| >> |
| >>-- A transpose within a subquery with correlated values |
| >>-- within the transpose expressions. |
| >> |
| >> select t1.a, t2.b |
| +> from t023t1 t1, t023t2 t2 |
| +> where t1.a = t2.a |
| +> and t1.c < (select sum(c1) |
| +> from t023t3 t3 |
| +> where t3.c < t2.d |
| +> transpose t1.a, t2.b as c1 |
| +> key by k1); |
| |
| A B |
| ----------- ----------- |
| |
| 1 1 |
| 2 2 |
| 3 3 |
| 4 4 |
| 5 5 |
| 6 6 |
| 7 7 |
| 8 8 |
| 9 9 |
| |
| --- 9 row(s) selected. |
| >> |
| >>-- This query could create a VEG between t1.k1 and t2.k2 |
| >>-- but, for now, VEG's are not created between ValueIDUnion nodes. |
| >>-- To enable VEGRefs for ValueIDUnion nodes, change ValueIdUnion:: |
| >>-- normalizeNode() to replace with a VegRef (if possible). |
| >>-- To allow ValueIdUnion nodes in a VEG, alter isAColumnReference() |
| >>-- to include ITM_VALUEIDUNION. |
| >>-- |
| >> select * |
| +> from (select k1,c1 |
| +> from t023t1 |
| +> transpose a,b,c as c1 |
| +> key by k1) as t1, |
| +> (select k2,c2 |
| +> from t023t1 |
| +> transpose c,b,a as c2 |
| +> key by k2) as t2 |
| +> where t1.k1 = t2.k2 |
| +> and t1.k1 < 3; |
| |
| K1 C1 K2 C2 |
| ----------- ----------- ----------- ----------- |
| |
| 1 0 1 0 |
| 1 0 1 900 |
| 1 0 1 800 |
| 1 0 1 700 |
| 1 0 1 600 |
| 1 0 1 500 |
| 1 0 1 400 |
| 1 0 1 300 |
| 1 0 1 200 |
| 1 0 1 100 |
| 2 0 2 0 |
| 2 0 2 90 |
| 2 0 2 80 |
| 2 0 2 70 |
| 2 0 2 60 |
| 2 0 2 50 |
| 2 0 2 40 |
| 2 0 2 30 |
| 2 0 2 20 |
| 2 0 2 10 |
| 1 1 1 0 |
| 1 1 1 900 |
| 1 1 1 800 |
| 1 1 1 700 |
| 1 1 1 600 |
| 1 1 1 500 |
| 1 1 1 400 |
| 1 1 1 300 |
| 1 1 1 200 |
| 1 1 1 100 |
| 2 10 2 0 |
| 2 10 2 90 |
| 2 10 2 80 |
| 2 10 2 70 |
| 2 10 2 60 |
| 2 10 2 50 |
| 2 10 2 40 |
| 2 10 2 30 |
| 2 10 2 20 |
| 2 10 2 10 |
| 1 2 1 0 |
| 1 2 1 900 |
| 1 2 1 800 |
| 1 2 1 700 |
| 1 2 1 600 |
| 1 2 1 500 |
| 1 2 1 400 |
| 1 2 1 300 |
| 1 2 1 200 |
| 1 2 1 100 |
| 2 20 2 0 |
| 2 20 2 90 |
| 2 20 2 80 |
| 2 20 2 70 |
| 2 20 2 60 |
| 2 20 2 50 |
| 2 20 2 40 |
| 2 20 2 30 |
| 2 20 2 20 |
| 2 20 2 10 |
| 1 3 1 0 |
| 1 3 1 900 |
| 1 3 1 800 |
| 1 3 1 700 |
| 1 3 1 600 |
| 1 3 1 500 |
| 1 3 1 400 |
| 1 3 1 300 |
| 1 3 1 200 |
| 1 3 1 100 |
| 2 30 2 0 |
| 2 30 2 90 |
| 2 30 2 80 |
| 2 30 2 70 |
| 2 30 2 60 |
| 2 30 2 50 |
| 2 30 2 40 |
| 2 30 2 30 |
| 2 30 2 20 |
| 2 30 2 10 |
| 1 4 1 0 |
| 1 4 1 900 |
| 1 4 1 800 |
| 1 4 1 700 |
| 1 4 1 600 |
| 1 4 1 500 |
| 1 4 1 400 |
| 1 4 1 300 |
| 1 4 1 200 |
| 1 4 1 100 |
| 2 40 2 0 |
| 2 40 2 90 |
| 2 40 2 80 |
| 2 40 2 70 |
| 2 40 2 60 |
| 2 40 2 50 |
| 2 40 2 40 |
| 2 40 2 30 |
| 2 40 2 20 |
| 2 40 2 10 |
| 1 5 1 0 |
| 1 5 1 900 |
| 1 5 1 800 |
| 1 5 1 700 |
| 1 5 1 600 |
| 1 5 1 500 |
| 1 5 1 400 |
| 1 5 1 300 |
| 1 5 1 200 |
| 1 5 1 100 |
| 2 50 2 0 |
| 2 50 2 90 |
| 2 50 2 80 |
| 2 50 2 70 |
| 2 50 2 60 |
| 2 50 2 50 |
| 2 50 2 40 |
| 2 50 2 30 |
| 2 50 2 20 |
| 2 50 2 10 |
| 1 6 1 0 |
| 1 6 1 900 |
| 1 6 1 800 |
| 1 6 1 700 |
| 1 6 1 600 |
| 1 6 1 500 |
| 1 6 1 400 |
| 1 6 1 300 |
| 1 6 1 200 |
| 1 6 1 100 |
| 2 60 2 0 |
| 2 60 2 90 |
| 2 60 2 80 |
| 2 60 2 70 |
| 2 60 2 60 |
| 2 60 2 50 |
| 2 60 2 40 |
| 2 60 2 30 |
| 2 60 2 20 |
| 2 60 2 10 |
| 1 7 1 0 |
| 1 7 1 900 |
| 1 7 1 800 |
| 1 7 1 700 |
| 1 7 1 600 |
| 1 7 1 500 |
| 1 7 1 400 |
| 1 7 1 300 |
| 1 7 1 200 |
| 1 7 1 100 |
| 2 70 2 0 |
| 2 70 2 90 |
| 2 70 2 80 |
| 2 70 2 70 |
| 2 70 2 60 |
| 2 70 2 50 |
| 2 70 2 40 |
| 2 70 2 30 |
| 2 70 2 20 |
| 2 70 2 10 |
| 1 8 1 0 |
| 1 8 1 900 |
| 1 8 1 800 |
| 1 8 1 700 |
| 1 8 1 600 |
| 1 8 1 500 |
| 1 8 1 400 |
| 1 8 1 300 |
| 1 8 1 200 |
| 1 8 1 100 |
| 2 80 2 0 |
| 2 80 2 90 |
| 2 80 2 80 |
| 2 80 2 70 |
| 2 80 2 60 |
| 2 80 2 50 |
| 2 80 2 40 |
| 2 80 2 30 |
| 2 80 2 20 |
| 2 80 2 10 |
| 1 9 1 0 |
| 1 9 1 900 |
| 1 9 1 800 |
| 1 9 1 700 |
| 1 9 1 600 |
| 1 9 1 500 |
| 1 9 1 400 |
| 1 9 1 300 |
| 1 9 1 200 |
| 1 9 1 100 |
| 2 90 2 0 |
| 2 90 2 90 |
| 2 90 2 80 |
| 2 90 2 70 |
| 2 90 2 60 |
| 2 90 2 50 |
| 2 90 2 40 |
| 2 90 2 30 |
| 2 90 2 20 |
| 2 90 2 10 |
| |
| --- 200 row(s) selected. |
| >> |
| >>-- This is the type of query that may be generated when scanning a packed |
| >>-- vertically partitioned table. Here we simulate four VP's with the tables |
| >>-- t023t4, t023t5, t023t6, and t023t7. Each of the tables contains the data |
| >>-- for one column of the 'base' table. The VP's are also packed with the |
| >>-- packing factors of 4, 8, 8, and 16 for the VP's respectively. The following |
| >>-- query first unpacks each VP to a packing factor of 4 then joins the |
| >>-- results to form a packed (non-VP) table. This result is then unpacked to |
| >>-- a packing factor of 1 (non-packed). Unpacking is accomplished through use |
| >>-- of the Transpose operator. |
| >> |
| >>select a,b,c,d |
| +>from |
| +>(select (skp + (k - 1)) as skp2, numRowsp2, a,b,c, d |
| +> from |
| +> (select VP1.skp, VP1.numRowsp, ap0, bp0, cp0, dp0, |
| +> ap1, bp1, cp1, dp1, |
| +> ap2, bp2, cp2, dp2, |
| +> ap3, bp3, cp3, dp3 |
| +> from (select sk as skp, numRows as numRowsp, |
| +> a0 as ap0, a1 as ap1, a2 as ap2, a3 as ap3 |
| +> from t023t4) as VP1, |
| +> (select sk + ((k - 1) * 4) as skp, numRowsp, bp0, bp1, bp2, bp3 |
| +> from t023t5 |
| +> transpose (case when numRows > 4 then 4 else numRows end, |
| +> b0,b1,b2,b3), |
| +> (case when numRows > 4 then numRows - 4 else 0 end, |
| +> b4,b5,b6,b7) |
| +> as (numRowsp, bp0,bp1,bp2,bp3) |
| +> key by k) as VP2, |
| +> (select sk + ((k - 1) * 4) as skp, numRowsp, cp0, cp1, cp2, cp3 |
| +> from t023t6 |
| +> transpose (case when numRows > 4 then 4 else numRows end, |
| +> c0,c1,c2,c3), |
| +> (case when numRows > 4 then numRows - 4 else 0 end, |
| +> c4,c5,c6,c7) |
| +> as (numRowsp, cp0,cp1,cp2,cp3) |
| +> key by k) as VP3, |
| +> (select sk + ((k - 1) * 4) as skp, numRowsp, dp0, dp1, dp2, dp3 |
| +> from t023t7 |
| +> transpose (case when numRows > 4 then 4 else numRows end, |
| +> d0,d1,d2,d3), |
| +> (case when numRows > 8 then 4 else |
| +> case when numRows > 4 then numRows - 4 else 0 end end, |
| +> d4,d5,d6,d7), |
| +> (case when numRows > 12 then 4 else |
| +> case when numRows > 8 then numRows - 8 else 0 end end, |
| +> d8,d9,d10,d11), |
| +> (case when numRows > 12 then numRows - 12 else 0 end, |
| +> d12,d13,d14,d15) |
| +> as (numRowsp, dp0,dp1,dp2,dp3) |
| +> key by k) as VP4 |
| +> |
| +> where VP1.skp = VP2.skp |
| +> and VP2.skp = VP3.skp |
| +> and VP3.skp = VP4.skp |
| +> ) as P1 |
| +> transpose (case when numRowsp > 0 then 1 else 0 end, |
| +> ap0, bp0, cp0, dp0), |
| +> (case when numRowsp > 1 then 1 else 0 end, |
| +> ap1, bp1, cp1, dp1), |
| +> (case when numRowsp > 2 then 1 else 0 end, |
| +> ap2, bp2, cp2, dp2), |
| +> (case when numRowsp > 3 then 1 else 0 end, |
| +> ap3, bp3, cp3, dp3) as (numRowsp2, a,b,c,d) |
| +> key by k) as P2 |
| +>where numRowsp2 = 1; |
| |
| A B C D |
| ----------- ----------- ----------- ----------- |
| |
| 0 100 200 300 |
| 1 101 201 301 |
| 2 102 202 302 |
| 3 103 203 303 |
| 4 104 204 304 |
| 5 105 205 305 |
| 6 106 206 306 |
| 7 107 207 307 |
| 8 108 208 308 |
| 9 109 209 309 |
| 10 110 210 310 |
| 11 111 211 311 |
| 12 112 212 312 |
| 13 113 213 313 |
| 14 114 214 314 |
| 15 115 215 315 |
| 16 116 216 316 |
| 17 117 217 317 |
| 18 118 218 318 |
| 19 119 219 319 |
| 20 120 220 320 |
| 21 121 221 321 |
| 22 122 222 322 |
| 23 123 223 323 |
| 24 124 224 324 |
| 25 125 225 325 |
| 26 126 226 326 |
| 27 127 227 327 |
| 28 128 228 328 |
| 29 129 229 329 |
| 30 130 230 330 |
| 31 131 231 331 |
| 32 132 232 332 |
| 33 133 233 333 |
| 34 134 234 334 |
| 35 135 235 335 |
| |
| --- 36 row(s) selected. |
| >> |
| >> |
| >>?section clnup |
| >>-- CLEANUP database |
| >>drop table t023t1; |
| |
| --- SQL operation complete. |
| >>drop table t023t2; |
| |
| --- SQL operation complete. |
| >>drop table t023t3; |
| |
| --- SQL operation complete. |
| >> |
| >>drop table t023t4; |
| |
| --- SQL operation complete. |
| >>drop table t023t5; |
| |
| --- SQL operation complete. |
| >>drop table t023t6; |
| |
| --- SQL operation complete. |
| >>drop table t023t7; |
| |
| --- SQL operation complete. |
| >> |
| >>log; |
