@@ -68,3 +68,116 @@ aggregate function.
6868 <td>Same as <code>EVERY</code></td>
6969 </tr >
7070</table >
71+
72+ If ` FILTER ` is specified, then only the input rows for which the
73+ filter_clause evaluates to true are fed to the aggregate function;
74+ other rows are discarded. For example:
75+
76+ ``` sql
77+ SELECT
78+ count (* ) AS unfiltered,
79+ count (* ) FILTER (WHERE i < 5 ) AS filtered
80+ FROM TABLE
81+ ```
82+
83+ ## Pivots
84+
85+ The SQL ` PIVOT ` operation can be used to turn rows into columns. It
86+ usually replaces a ` GROUP-BY ` operation when the group keys are known
87+ in advance. Instead of producing one row for each group, ` PIVOT ` can
88+ produce one * column* for each group.
89+
90+ ### Syntax
91+
92+ ```
93+ PIVOT ( { aggregate_expression [ AS aggregate_expression_alias ] } [ , ... ]
94+ FOR column_with_data IN ( column_list ) )
95+ ```
96+
97+ ### Parameters
98+
99+ - aggregate_expression
100+ Specifies an aggregate expression (` SUM ` , ` COUNT(DISTINCT ) ` , etc.).
101+
102+ - aggregate_expression_alias
103+ Specifies a column name for the aggregate expression.
104+
105+ - column_with_data
106+ A column that produces all the values that will become new
107+ column names.
108+
109+ - column_list
110+ Columns that show the pivoted data.
111+
112+ ### Example
113+
114+ ``` sql
115+ CREATE TABLE FURNITURE (
116+ type VARCHAR ,
117+ year INTEGER ,
118+ count INTEGER
119+ );
120+ INSERT INTO FURNITURE VALUES
121+ (' chair' 2020 , 4 ),
122+ (' table' 2021 , 3 ),
123+ (' chair' 2021 , 4 ),
124+ (' desk' 2023 , 1 ),
125+ (' table' 2023 , 2 );
126+
127+ SELECT year, type, SUM (count) FROM FURNITURE GROUP BY year,type;
128+ year | type | sum
129+ -- -----------------
130+ 2020 | chair | 4
131+ 2021 | table | 3
132+ 2021 | chair | 4
133+ 2023 | desk | 1
134+ 2023 | table | 2
135+ (5 rows)
136+
137+ SELECT * FROM FURNITURE
138+ PIVOT (
139+ SUM (count) AS ct
140+ FOR type IN (' desk' AS desks, ' table' AS tables, ' chair' as chairs)
141+ );
142+
143+ year | desks | tables | chairs
144+ -- ----------------------------
145+ 2020 | | | 4
146+ 2021 | | 3 | 4
147+ 2023 | 1 | 2 |
148+ (3 rows)
149+ ```
150+
151+ Notice how the same information is presented in a tabular form where
152+ we have a column for each type of object. PIVOTs require all the
153+ possible "type"s to be specified when the query is written. Notice
154+ that if we add an additional type, the ` GROUP BY ` query will produce a
155+ correct result, while the ` PIVOT ` query will produce the same result.
156+
157+ ``` sql
158+ INSERT INTO FURNITURE VALUES (' bed' 2020 , 5 );
159+ SELECT year, type, SUM (count) FROM FURNITURE GROUP BY year,type;
160+ year | type | sum
161+ -- -----------------
162+ 2020 | chair | 4
163+ 2020 | bed | 5
164+ 2021 | table | 3
165+ 2021 | chair | 4
166+ 2023 | desk | 1
167+ 2023 | table | 2
168+ (6 rows)
169+
170+ SELECT * FROM FURNITURE
171+ PIVOT (
172+ SUM (count) AS ct
173+ FOR type IN (' desk' AS desks, ' table' AS tables, ' chair' as chairs)
174+ );
175+
176+ year | desks | tables | chairs
177+ -- ----------------------------
178+ 2020 | | | 4
179+ 2021 | | 3 | 4
180+ 2023 | 1 | 2 |
181+ (3 rows)
182+ ```
183+
0 commit comments