diff options
Diffstat (limited to 'storage/connect/mysql-test/connect/r/pivot.result')
| -rw-r--r-- | storage/connect/mysql-test/connect/r/pivot.result | 172 |
1 files changed, 171 insertions, 1 deletions
diff --git a/storage/connect/mysql-test/connect/r/pivot.result b/storage/connect/mysql-test/connect/r/pivot.result index 8a5151a4a15..11eee6233a4 100644 --- a/storage/connect/mysql-test/connect/r/pivot.result +++ b/storage/connect/mysql-test/connect/r/pivot.result @@ -1,5 +1,5 @@ # -# PIVOT table type is temporarily not supported +# Testing the PIVOT table type # CREATE TABLE expenses ( Who CHAR(10) NOT NULL, @@ -33,6 +33,116 @@ Joe 5 Food 12.00 Janet 3 Beer 18.00 Janet 4 Car 17.00 Janet 5 Food 12.00 +# +# Pivoting from What +# +CREATE TABLE pivex ( +Who CHAR(10) NOT NULL, +Week INT(2) NOT NULL, +Beer DOUBLE(8,2) FLAG=1, +Car DOUBLE(8,2) FLAG=1, +Food DOUBLE(8,2) FLAG=1) +ENGINE=CONNECT TABLE_TYPE=PIVOT TABNAME=expenses; +ALTER TABLE pivex OPTION_LIST='port=PORT'; +Warnings: +Warning 1105 The current version of CONNECT did not check what you changed in ALTER. Use at your own risk +SELECT * FROM pivex; +Who Week Beer Car Food +Beth 3 16.00 0.00 0.00 +Beth 4 15.00 0.00 17.00 +Beth 5 20.00 0.00 12.00 +Janet 3 18.00 19.00 18.00 +Janet 4 0.00 17.00 0.00 +Janet 5 33.00 12.00 12.00 +Joe 3 18.00 20.00 31.00 +Joe 4 49.00 0.00 34.00 +Joe 5 14.00 0.00 12.00 +# +# Restricting the columns in a Pivot Table +# +ALTER TABLE pivex DROP COLUMN week; +SELECT * FROM pivex; +Who Beer Car Food +Beth 51.00 0.00 29.00 +Janet 51.00 48.00 30.00 +Joe 81.00 20.00 77.00 +# +# Using a source definition +# +DROP TABLE pivex; +CREATE TABLE pivex ( +Who CHAR(10) NOT NULL, +Week INT(2) NOT NULL, +Beer DOUBLE(8,2) FLAG=1, +Car DOUBLE(8,2) FLAG=1, +Food DOUBLE(8,2) FLAG=1) +ENGINE=CONNECT TABLE_TYPE=PIVOT +SRCDEF='select who, week, what, sum(amount) as amount from expenses where week in (4,5) group by who, week, what'; +Warnings: +Warning 1105 Cannot check looping reference +ALTER TABLE pivex OPTION_LIST='PivotCol=what,FncCol=amount,port=PORT'; +Warnings: +Warning 1105 The current version of CONNECT did not check what you changed in ALTER. Use at your own risk +SELECT * FROM pivex; +Who Week Beer Car Food +Beth 4 15.00 0.00 17.00 +Beth 5 20.00 0.00 12.00 +Janet 4 0.00 17.00 0.00 +Janet 5 33.00 12.00 12.00 +Joe 4 49.00 0.00 34.00 +Joe 5 14.00 0.00 12.00 +# +# Pivoting from Week +# +DROP TABLE pivex; +CREATE TABLE pivex ( +Who CHAR(10) NOT NULL, +What CHAR(12) NOT NULL, +`3` DOUBLE(8,2) FLAG=1, +`4` DOUBLE(8,2) FLAG=1, +`5` DOUBLE(8,2) FLAG=1) +ENGINE=CONNECT TABLE_TYPE=PIVOT TABNAME=expenses; +ALTER TABLE pivex OPTION_LIST='PivotCol=Week,port=PORT'; +Warnings: +Warning 1105 The current version of CONNECT did not check what you changed in ALTER. Use at your own risk +SELECT * FROM pivex; +Who What 3 4 5 +Beth Beer 16.00 15.00 20.00 +Beth Food 0.00 17.00 12.00 +Janet Beer 18.00 0.00 33.00 +Janet Car 19.00 17.00 12.00 +Janet Food 18.00 0.00 12.00 +Joe Beer 18.00 49.00 14.00 +Joe Car 20.00 0.00 0.00 +Joe Food 31.00 34.00 12.00 +# +# Using scalar functions and expresssions +# +DROP TABLE pivex; +CREATE TABLE pivex ( +Who CHAR(10) NOT NULL, +What CHAR(12) NOT NULL, +First DOUBLE(8,2) FLAG=1, +Middle DOUBLE(8,2) FLAG=1, +Last DOUBLE(8,2) FLAG=1) +ENGINE=CONNECT TABLE_TYPE=PIVOT +SRCDEF='select who, what, case when week=3 then ''First'' when week=5 then ''Last'' else ''Middle'' end as wk, sum(amount) * 6.56 as amnt from expenses group by who, what, wk'; +Warnings: +Warning 1105 Cannot check looping reference +ALTER TABLE pivex OPTION_LIST='PivotCol=wk,FncCol=amnt,port=PORT'; +Warnings: +Warning 1105 The current version of CONNECT did not check what you changed in ALTER. Use at your own risk +SELECT * FROM pivex; +Who What First Middle Last +Beth Beer 104.96 98.40 131.20 +Beth Food 0.00 111.52 78.72 +Janet Beer 118.08 0.00 216.48 +Janet Car 124.64 111.52 78.72 +Janet Food 118.08 0.00 78.72 +Joe Beer 118.08 321.44 91.84 +Joe Car 131.20 0.00 0.00 +Joe Food 203.36 223.04 78.72 +DROP TABLE pivex; DROP TABLE expenses; # # Make the PETS table @@ -61,4 +171,64 @@ Kevin cat 2 Kevin bird 6 Donald dog 1 Donald fish 3 +# +# Pivot the PETS table +# +CREATE TABLE pivet ( +name VARCHAR(12) NOT NULL, +dog INT NOT NULL DEFAULT 0 FLAG=1, +cat INT NOT NULL DEFAULT 0 FLAG=1, +rabbit INT NOT NULL DEFAULT 0 FLAG=1, +bird INT NOT NULL DEFAULT 0 FLAG=1, +fish INT NOT NULL DEFAULT 0 FLAG=1) +ENGINE=CONNECT TABLE_TYPE=PIVOT TABNAME=pets OPTION_LIST='PivotCol=race,groupby=1'; +SELECT * FROM pivet; +name dog cat rabbit bird fish +John 2 0 0 0 0 +Bill 0 1 0 0 0 +Mary 1 1 0 0 0 +Lisbeth 0 0 2 0 0 +Kevin 0 2 0 6 0 +Donald 1 0 0 0 3 +DROP TABLE pivet; +# +# Testing the "data" column list +# +CREATE TABLE pivet ( +name VARCHAR(12) NOT NULL, +dog INT NOT NULL DEFAULT 0 FLAG=1, +cat INT NOT NULL DEFAULT 0 FLAG=1) +ENGINE=CONNECT TABLE_TYPE=PIVOT TABNAME=pets OPTION_LIST='PivotCol=race,groupby=1'; +SELECT * FROM pivet; +ERROR HY000: Got error 122 'Cannot find matching column' from CONNECT +ALTER TABLE pivet OPTION_LIST='PivotCol=race,groupby=1,accept=1'; +Warnings: +Warning 1105 The current version of CONNECT did not check what you changed in ALTER. Use at your own risk +SELECT * FROM pivet; +name dog cat +John 2 0 +Bill 0 1 +Mary 1 1 +Lisbeth 0 0 +Kevin 0 2 +Donald 1 0 +DROP TABLE pivet; +# +# Adding a "dump" column +# +CREATE TABLE pivet ( +name VARCHAR(12) NOT NULL, +dog INT NOT NULL DEFAULT 0 FLAG=1, +cat INT NOT NULL DEFAULT 0 FLAG=1, +other INT NOT NULL DEFAULT 0 FLAG=2) +ENGINE=CONNECT TABLE_TYPE=PIVOT TABNAME=pets OPTION_LIST='PivotCol=race,groupby=1'; +SELECT * FROM pivet; +name dog cat other +John 2 0 0 +Bill 0 1 0 +Mary 1 1 0 +Lisbeth 0 0 2 +Kevin 0 2 6 +Donald 1 0 3 +DROP TABLE pivet; DROP TABLE pets; |