1-- 2-- \crosstabview 3-- 4 5CREATE TABLE ctv_data (v, h, c, i, d) AS 6VALUES 7 ('v1','h2','foo', 3, '2015-04-01'::date), 8 ('v2','h1','bar', 3, '2015-01-02'), 9 ('v1','h0','baz', NULL, '2015-07-12'), 10 ('v0','h4','qux', 4, '2015-07-15'), 11 ('v0','h4','dbl', -3, '2014-12-15'), 12 ('v0',NULL,'qux', 5, '2014-07-15'), 13 ('v1','h2','quux',7, '2015-04-04'); 14 15-- make plans more stable 16ANALYZE ctv_data; 17 18-- running \crosstabview after query uses query in buffer 19SELECT v, EXTRACT(year FROM d), count(*) 20 FROM ctv_data 21 GROUP BY 1, 2 22 ORDER BY 1, 2; 23-- basic usage with 3 columns 24 \crosstabview 25 26-- ordered months in horizontal header, quoted column name 27SELECT v, to_char(d, 'Mon') AS "month name", EXTRACT(month FROM d) AS num, 28 count(*) FROM ctv_data GROUP BY 1,2,3 ORDER BY 1 29 \crosstabview v "month name" 4 num 30 31-- ordered months in vertical header, ordered years in horizontal header 32SELECT EXTRACT(year FROM d) AS year, to_char(d,'Mon') AS """month"" name", 33 EXTRACT(month FROM d) AS month, 34 format('sum=%s avg=%s', sum(i), avg(i)::numeric(2,1)) 35 FROM ctv_data 36 GROUP BY EXTRACT(year FROM d), to_char(d,'Mon'), EXTRACT(month FROM d) 37ORDER BY month 38\crosstabview """month"" name" year format year 39 40-- combine contents vertically into the same cell (V/H duplicates) 41SELECT v, h, string_agg(c, E'\n') FROM ctv_data GROUP BY v, h ORDER BY 1,2,3 42 \crosstabview 1 2 3 43 44-- horizontal ASC order from window function 45SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h) AS r 46FROM ctv_data GROUP BY v, h ORDER BY 1,3,2 47 \crosstabview v h c r 48 49-- horizontal DESC order from window function 50SELECT v, h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h DESC) AS r 51FROM ctv_data GROUP BY v, h ORDER BY 1,3,2 52 \crosstabview v h c r 53 54-- horizontal ASC order from window function, NULLs pushed rightmost 55SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h NULLS LAST) AS r 56FROM ctv_data GROUP BY v, h ORDER BY 1,3,2 57 \crosstabview v h c r 58 59-- only null, no column name, 2 columns: error 60SELECT null,null \crosstabview 61 62-- only null, no column name, 3 columns: works 63SELECT null,null,null \crosstabview 64 65-- null display 66\pset null '#null#' 67SELECT v,h, string_agg(i::text, E'\n') AS i FROM ctv_data 68GROUP BY v, h ORDER BY h,v 69 \crosstabview v h i 70\pset null '' 71 72-- refer to columns by position 73SELECT v,h,string_agg(i::text, E'\n'), string_agg(c, E'\n') 74FROM ctv_data GROUP BY v, h ORDER BY h,v 75 \crosstabview 2 1 4 76 77-- refer to columns by positions and names mixed 78SELECT v,h, string_agg(i::text, E'\n') AS i, string_agg(c, E'\n') AS c 79FROM ctv_data GROUP BY v, h ORDER BY h,v 80 \crosstabview 1 "h" 4 81 82-- refer to columns by quoted names, check downcasing of unquoted name 83SELECT 1 as "22", 2 as b, 3 as "Foo" 84 \crosstabview "22" B "Foo" 85 86-- error: bad column name 87SELECT v,h,c,i FROM ctv_data 88 \crosstabview v h j 89 90-- error: need to quote name 91SELECT 1 as "22", 2 as b, 3 as "Foo" 92 \crosstabview 1 2 Foo 93 94-- error: need to not quote name 95SELECT 1 as "22", 2 as b, 3 as "Foo" 96 \crosstabview 1 "B" "Foo" 97 98-- error: bad column number 99SELECT v,h,i,c FROM ctv_data 100 \crosstabview 2 1 5 101 102-- error: same H and V columns 103SELECT v,h,i,c FROM ctv_data 104 \crosstabview 2 h 4 105 106-- error: too many columns 107SELECT a,a,1 FROM generate_series(1,3000) AS a 108 \crosstabview 109 110-- error: only one column 111SELECT 1 \crosstabview 112 113DROP TABLE ctv_data; 114 115-- check error reporting (bug #14476) 116CREATE TABLE ctv_data (x int, y int, v text); 117 118INSERT INTO ctv_data SELECT 1, x, '*' || x FROM generate_series(1,10) x; 119SELECT * FROM ctv_data \crosstabview 120 121INSERT INTO ctv_data VALUES (1, 10, '*'); -- duplicate data to cause error 122SELECT * FROM ctv_data \crosstabview 123 124DROP TABLE ctv_data; 125