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