ektravel commented on code in PR #15669:
URL: https://github.com/apache/druid/pull/15669#discussion_r1466659257
##########
docs/querying/sql.md:
##########
@@ -85,6 +87,121 @@ FROM clause, metadata tables are not considered
datasources. They exist only in
For more information about table, lookup, query, and join datasources, refer
to the [Datasources](datasource.md)
documentation.
+## PIVOT
+
+:::info
+The PIVOT operator is an [experimental
feature](../development/experimental-features.md).
+:::
+
+The PIVOT operator carries out an aggregation and transforms rows into columns
in the output.
+
+The following is the general syntax for the PIVOT operator. Note that the
PIVOT operator is enclosed in parentheses and forms part of the FROM clause of
the query.
+
+```sql
+PIVOT (aggregation_function(column_to_aggregate)
+ FOR column_with_values_to_pivot
+ IN (pivoted_column1 [, pivoted_column2 ...])
+)
+```
+
+PIVOT syntax parameters:
+
+* `aggregation_function`: An aggregation function, such as SUM, COUNT, MIN,
MAX, or AVG.
+* `column_to_aggregate`: The source column to be aggregated.
+* `column_with_values_to_pivot`: The column that contains values for the
pivoted column names.
+* `pivoted_columnN`: The list of values to pivot into headers in the output.
+
+The following example demonstrates how to transform `cityName` values into
column headers `ba_sum_deleted` and `ny_sum_deleted`:
+
+```sql
+SELECT user, channel, ba_sum_deleted, ny_sum_deleted
+FROM "wikipedia"
+PIVOT (SUM(deleted) AS "sum_deleted" FOR "cityName" IN ( 'Buenos Aires' AS ba,
'New York' AS ny))
+WHERE ba_sum_deleted IS NOT NULL OR ny_sum_deleted IS NOT NULL
+LIMIT 15
+```
+
+<details>
+<summary> View results </summary>
+
+|`user`|`channel`|`ba_sum_deleted`|`ny_sum_deleted`|
+|------|---------|----------------|----------------|
+|181.230.118.178|`#en.wikipedia`|0|null|
+|181.230.118.178|`#en.wikipedia`|0|null|
+|69.86.6.150|`#en.wikipedia`|null|1|
+|190.123.145.147|`#es.wikipedia`|0|null|
+|181.230.118.178|`#en.wikipedia`|16|null|
+|181.230.118.178|`#en.wikipedia`|0|null|
+|181.230.118.178|`#en.wikipedia`|0|null|
+|181.230.118.178|`#en.wikipedia`|0|null|
+|181.230.118.178|`#en.wikipedia`|0|null|
+|181.230.118.178|`#en.wikipedia`|0|null|
+|181.230.118.178|`#en.wikipedia`|0|null|
+|190.192.179.192|`#en.wikipedia`|0|null|
+|181.230.118.178|`#en.wikipedia`|0|null|
+|181.230.118.178|`#en.wikipedia`|0|null|
+|181.230.118.178|`#en.wikipedia`|0|null|
+
+</details>
+
+## UNPIVOT
+
+:::info
+The UNPIVOT operator is an [experimental
feature](../development/experimental-features.md).
+:::
+
+The UNPIVOT operator transforms existing column values into rows.
+Note that UNPIVOT isn't the exact reverse operation of PIVOT. The PIVOT
operator carries out an aggregation and merges rows as needed. UNPIVOT doesn't
reproduce the original rows that have been merged.
+
+The following is the general syntax for the UNPIVOT operator. Note that the
UNPIVOT operator is enclosed in parentheses and forms part of the FROM clause
of the query.
+
+```sql
+UNPIVOT (values_column
+ FOR names_column
+ IN (unpivoted_column1 [, unpivoted_column2 ... ])
+)
+```
+
+UNPIVOT syntax parameters:
+
+* `values_column`: The column that contains the values of the unpivoted
columns.
+* `names_column`: The column that contains the names of the unpivoted columns.
+* `unpivoted_columnN`: The list of columns to transform into rows in the
output.
+
+The following example demonstrates how to transform the columns `added` and
`deleted` into row values that correspond to a particular `channel`:
+
+```sql
+SELECT channel, user, action, SUM(changes) AS total_changes
+FROM "wikipedia"
+UNPIVOT ( changes FOR action IN ("added", "deleted") )
+WHERE channel LIKE '#ar%'
+GROUP BY channel, user, action
+LIMIT 15
+```
+
+<details>
+<summary> View results </summary>
+
+|`channel`|`user`|`action`|`total_changes`|
+|---------|------|--------|---------------|
+|`#ar.wikipedia`|156.202.189.223|added|0|
+|`#ar.wikipedia`|156.202.189.223|deleted|30|
+|`#ar.wikipedia`|156.202.76.160|added|0|
+|`#ar.wikipedia`|156.202.76.160|deleted|0|
+|`#ar.wikipedia`|156.212.124.165|added|451|
+|`#ar.wikipedia`|156.212.124.165|deleted|0|
+|`#ar.wikipedia`|160.166.147.167|added|1|
+|`#ar.wikipedia`|160.166.147.167|deleted|0|
+|`#ar.wikipedia`|185.99.32.50|added|1|
+|`#ar.wikipedia`|185.99.32.50|deleted|0|
+|`#ar.wikipedia`|197.18.109.148|added|0|
+|`#ar.wikipedia`|197.18.109.148|deleted|24|
+|`#ar.wikipedia`|2001:16A2:3C7:6C00:917E:AD28:FAD3:FD5C|added|1|
Review Comment:
```suggestion
|`#ar.wikipedia`|`2001:16A2:3C7:6C00:917E:AD28:FAD3:FD5C`|added|1|
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
