1-- 2-- \crosstabview 3-- 4CREATE TABLE ctv_data (v, h, c, i, d) AS 5VALUES 6 ('v1','h2','foo', 3, '2015-04-01'::date), 7 ('v2','h1','bar', 3, '2015-01-02'), 8 ('v1','h0','baz', NULL, '2015-07-12'), 9 ('v0','h4','qux', 4, '2015-07-15'), 10 ('v0','h4','dbl', -3, '2014-12-15'), 11 ('v0',NULL,'qux', 5, '2014-07-15'), 12 ('v1','h2','quux',7, '2015-04-04'); 13-- make plans more stable 14ANALYZE ctv_data; 15-- running \crosstabview after query uses query in buffer 16SELECT v, EXTRACT(year FROM d), count(*) 17 FROM ctv_data 18 GROUP BY 1, 2 19 ORDER BY 1, 2; 20 v | date_part | count 21----+-----------+------- 22 v0 | 2014 | 2 23 v0 | 2015 | 1 24 v1 | 2015 | 3 25 v2 | 2015 | 1 26(4 rows) 27 28-- basic usage with 3 columns 29 \crosstabview 30 v | 2014 | 2015 31----+------+------ 32 v0 | 2 | 1 33 v1 | | 3 34 v2 | | 1 35(3 rows) 36 37-- ordered months in horizontal header, quoted column name 38SELECT v, to_char(d, 'Mon') AS "month name", EXTRACT(month FROM d) AS num, 39 count(*) FROM ctv_data GROUP BY 1,2,3 ORDER BY 1 40 \crosstabview v "month name" 4 num 41 v | Jan | Apr | Jul | Dec 42----+-----+-----+-----+----- 43 v0 | | | 2 | 1 44 v1 | | 2 | 1 | 45 v2 | 1 | | | 46(3 rows) 47 48-- ordered months in vertical header, ordered years in horizontal header 49SELECT EXTRACT(year FROM d) AS year, to_char(d,'Mon') AS """month"" name", 50 EXTRACT(month FROM d) AS month, 51 format('sum=%s avg=%s', sum(i), avg(i)::numeric(2,1)) 52 FROM ctv_data 53 GROUP BY EXTRACT(year FROM d), to_char(d,'Mon'), EXTRACT(month FROM d) 54ORDER BY month 55\crosstabview """month"" name" year format year 56 "month" name | 2014 | 2015 57--------------+-----------------+---------------- 58 Jan | | sum=3 avg=3.0 59 Apr | | sum=10 avg=5.0 60 Jul | sum=5 avg=5.0 | sum=4 avg=4.0 61 Dec | sum=-3 avg=-3.0 | 62(4 rows) 63 64-- combine contents vertically into the same cell (V/H duplicates) 65SELECT v, h, string_agg(c, E'\n') FROM ctv_data GROUP BY v, h ORDER BY 1,2,3 66 \crosstabview 1 2 3 67 v | h4 | | h0 | h2 | h1 68----+-----+-----+-----+------+----- 69 v0 | qux+| qux | | | 70 | dbl | | | | 71 v1 | | | baz | foo +| 72 | | | | quux | 73 v2 | | | | | bar 74(3 rows) 75 76-- horizontal ASC order from window function 77SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h) AS r 78FROM ctv_data GROUP BY v, h ORDER BY 1,3,2 79 \crosstabview v h c r 80 v | h0 | h1 | h2 | h4 | 81----+-----+-----+------+-----+----- 82 v0 | | | | qux+| qux 83 | | | | dbl | 84 v1 | baz | | foo +| | 85 | | | quux | | 86 v2 | | bar | | | 87(3 rows) 88 89-- horizontal DESC order from window function 90SELECT v, h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h DESC) AS r 91FROM ctv_data GROUP BY v, h ORDER BY 1,3,2 92 \crosstabview v h c r 93 v | | h4 | h2 | h1 | h0 94----+-----+-----+------+-----+----- 95 v0 | qux | qux+| | | 96 | | dbl | | | 97 v1 | | | foo +| | baz 98 | | | quux | | 99 v2 | | | | bar | 100(3 rows) 101 102-- horizontal ASC order from window function, NULLs pushed rightmost 103SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h NULLS LAST) AS r 104FROM ctv_data GROUP BY v, h ORDER BY 1,3,2 105 \crosstabview v h c r 106 v | h0 | h1 | h2 | h4 | 107----+-----+-----+------+-----+----- 108 v0 | | | | qux+| qux 109 | | | | dbl | 110 v1 | baz | | foo +| | 111 | | | quux | | 112 v2 | | bar | | | 113(3 rows) 114 115-- only null, no column name, 2 columns: error 116SELECT null,null \crosstabview 117\crosstabview: query must return at least three columns 118-- only null, no column name, 3 columns: works 119SELECT null,null,null \crosstabview 120 ?column? | 121----------+-- 122 | 123(1 row) 124 125-- null display 126\pset null '#null#' 127SELECT v,h, string_agg(i::text, E'\n') AS i FROM ctv_data 128GROUP BY v, h ORDER BY h,v 129 \crosstabview v h i 130 v | h0 | h1 | h2 | h4 | #null# 131----+--------+----+----+----+-------- 132 v1 | #null# | | 3 +| | 133 | | | 7 | | 134 v2 | | 3 | | | 135 v0 | | | | 4 +| 5 136 | | | | -3 | 137(3 rows) 138 139\pset null '' 140-- refer to columns by position 141SELECT v,h,string_agg(i::text, E'\n'), string_agg(c, E'\n') 142FROM ctv_data GROUP BY v, h ORDER BY h,v 143 \crosstabview 2 1 4 144 h | v1 | v2 | v0 145----+------+-----+----- 146 h0 | baz | | 147 h1 | | bar | 148 h2 | foo +| | 149 | quux | | 150 h4 | | | qux+ 151 | | | dbl 152 | | | qux 153(5 rows) 154 155-- refer to columns by positions and names mixed 156SELECT v,h, string_agg(i::text, E'\n') AS i, string_agg(c, E'\n') AS c 157FROM ctv_data GROUP BY v, h ORDER BY h,v 158 \crosstabview 1 "h" 4 159 v | h0 | h1 | h2 | h4 | 160----+-----+-----+------+-----+----- 161 v1 | baz | | foo +| | 162 | | | quux | | 163 v2 | | bar | | | 164 v0 | | | | qux+| qux 165 | | | | dbl | 166(3 rows) 167 168-- refer to columns by quoted names, check downcasing of unquoted name 169SELECT 1 as "22", 2 as b, 3 as "Foo" 170 \crosstabview "22" B "Foo" 171 22 | 2 172----+--- 173 1 | 3 174(1 row) 175 176-- error: bad column name 177SELECT v,h,c,i FROM ctv_data 178 \crosstabview v h j 179\crosstabview: column name not found: "j" 180-- error: need to quote name 181SELECT 1 as "22", 2 as b, 3 as "Foo" 182 \crosstabview 1 2 Foo 183\crosstabview: column name not found: "foo" 184-- error: need to not quote name 185SELECT 1 as "22", 2 as b, 3 as "Foo" 186 \crosstabview 1 "B" "Foo" 187\crosstabview: column name not found: "B" 188-- error: bad column number 189SELECT v,h,i,c FROM ctv_data 190 \crosstabview 2 1 5 191\crosstabview: column number 5 is out of range 1..4 192-- error: same H and V columns 193SELECT v,h,i,c FROM ctv_data 194 \crosstabview 2 h 4 195\crosstabview: vertical and horizontal headers must be different columns 196-- error: too many columns 197SELECT a,a,1 FROM generate_series(1,3000) AS a 198 \crosstabview 199\crosstabview: maximum number of columns (1600) exceeded 200-- error: only one column 201SELECT 1 \crosstabview 202\crosstabview: query must return at least three columns 203DROP TABLE ctv_data; 204-- check error reporting (bug #14476) 205CREATE TABLE ctv_data (x int, y int, v text); 206INSERT INTO ctv_data SELECT 1, x, '*' || x FROM generate_series(1,10) x; 207SELECT * FROM ctv_data \crosstabview 208 x | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 209---+----+----+----+----+----+----+----+----+----+----- 210 1 | *1 | *2 | *3 | *4 | *5 | *6 | *7 | *8 | *9 | *10 211(1 row) 212 213INSERT INTO ctv_data VALUES (1, 10, '*'); -- duplicate data to cause error 214SELECT * FROM ctv_data \crosstabview 215\crosstabview: query result contains multiple data values for row "1", column "10" 216DROP TABLE ctv_data; 217