Pivoting a the rows of a query’s result set into columns is magically simple using YSQLSH, YugabyteDB’s YSQL command line interface.
The \crosstabview command executes the current query buffer (like \g) and shows the results in a crosstab grid.
Example:
yugabyte=> CREATE TABLE my_data AS SELECT UPPER(randomstring(1)) vertical_data, UPPER(randomstring(1)) horizontal_data, randomdec(0, 100) some_data FROM generate_series(1, 10000);
SELECT 10000
yugabyte=> SELECT vertical_data, horizontal_data, SUM(some_data) some_data_sum FROM my_data WHERE vertical_data <= 'C' AND horizontal_data <= 'C' GROUP BY vertical_data, horizontal_data ORDER BY vertical_data, horizontal_data;
vertical_data | horizontal_data | some_data_sum
---------------+-----------------+---------------------
A | A | 237.0323053561151
A | B | 491.685244813561383
A | C | 484.66233946382999
B | A | 815.49032279290259
B | B | 980.81478448584675
B | C | 627.324046054854929
C | A | 767.62784412130716
C | B | 905.04765287041656
C | C | 1087.90952502749856
(9 rows)
Note that in the above CREATE TABLE statement, I used the functions randomstring and randomdec which were described in previous YugabyteDB tips!
Now using the \crosstabview command, we can pivot the above query result’s rows into columns!
yugabyte=> \crosstabview vertical_data horizontal_data some_data_sum;
vertical_data | A | B | C
---------------+--------------------+---------------------+---------------------
A | 237.0323053561151 | 491.685244813561383 | 484.66233946382999
B | 815.49032279290259 | 980.81478448584675 | 627.324046054854929
C | 767.62784412130716 | 905.04765287041656 | 1087.90952502749856
(3 rows)
Have Fun!
