loading..
   English
16:38

PostgreSQL CROSSTAB

In PostgreSQL, you can rotate a table using the CROSSTAB function. This function is passed a SQL query as a text parameter, which returns three columns:

  • row ID this column contains values identifying the resulting (rotated) row;
  • category unique values in this column determine the columns of the rotated table. Lets note that, unlike PIVOT , these values themselves are of no significance; important is their number only, since it specifies the maximal possible number of columns;
  • category value the actual category values. The values are put in columns from left to right, and the category names play no role just their order determined by the way the query is sorted is important.

Well explain this using the Painting database as an example.

Lets sum up the amount of paint of each color for each square:

Console
Execute
  1. SELECT b_q_id, v_color, SUM(b_vol) qty FROM utb JOIN utv ON b_v_id = v_id
  2. WHERE b_q_id BETWEEN 12 AND 16
  3. GROUP BY b_q_id, v_color
  4. ORDER BY b_q_id, CASE v_color WHEN 'R' THEN 1 WHEN 'G' THEN 2 ELSE 3 END;

Here, we confined ourselves to squares with IDs in the range between 12 to 16 in order to, on the one hand, keep the output small, yet, on the other hand, retain its representativeness. The colors are sorted in RGB order. Here is the result set:

b_q_id v_color Qty
12 R 255
12 G 255
12 B 255
13 B 123
14 R 50
14 B 111
15 R 100
15 G 100
16 G 100
16 B 150

In CROSSTAB terms, the spray can numbers represent row IDs, and the colors correspond to categories. The result of the table rotation should be as follows:

square R G B
12 255 255 255
13 123
14 50 111
15 100 100
16 100 150

Now, lets try to write a query using CROSSTAB that will return the desired result:

  1. SELECT * FROM
  2. crosstab(
  3. $$select b_q_id, v_color, SUM(b_vol) qty FROM utb JOIN utv ON b_v_id = v_id
  4. WHERE b_q_id BETWEEN 12 AND 16
  5. GROUP BY b_q_id, v_color
  6. ORDER BY b_q_id, CASE v_color WHEN 'R' THEN 1 WHEN 'G' THEN 2 ELSE 3 END; $$
  7. ) AS ct(square int, "R" bigint,"G" bigint,"B" bigint)
  8. ORDER BY square;

Here, we have to list all columns specifying their types. Yet some of the category columns may be omitted. Lets look at the result (you can check your queries using the console, and setting PostgreSQL for execution):

square R G B
12 255 255 255
13 123
14 50 111
15 100 100
16 100 150

This result isnt exactly what we expected. We recall that only the order of the categories matters. If the square has been painted with one color only, the value (total amount of paint) lands in the first category (named R in our query) no matter what color it actually was. Lets rewrite our query to make it return values for all colors, and that in the order needed. Missing colors should be replaced by NULL. To achieve this, well add for each square and color a row with a NULL in the paint amount field:

Console
Execute
  1. SELECT b_q_id, v_color, SUM(b_vol) qty FROM(
  2. SELECT b_q_id, v_color, b_vol FROM utb
  3. JOIN utv ON b_v_id = v_id
  4. UNION ALL --here addition is
  5. SELECT * FROM (SELECT DISTINCT b_q_id FROM utb) X
  6. CROSS JOIN (SELECT 'R' color, NULL::smallint vol
  7. UNION ALL SELECT 'G', NULL UNION ALL SELECT 'B', NULL) Y
  8. ) X
  9. WHERE b_q_id BETWEEN 12 AND 16
  10. GROUP BY b_q_id, v_color
  11. ORDER BY b_q_id, CASE v_color WHEN 'R' THEN 1 WHEN 'G' THEN 2 ELSE 3 END;

b_q_id v_color Qty
12 R 255
12 G 255
12 B 255
13 R
13 G
13 B 123
14 R 50
14 G
14 B 111
15 R 100
15 G 100
15 B
16 R
16 G 100
16 B 150

Now the query below will return the required result.

  1. SELECT * FROM
  2. crosstab(
  3. $$select b_q_id, v_color, SUM(b_vol) qty FROM(
  4. SELECT b_q_id, v_color, b_vol FROM utb
  5. JOIN utv ON b_v_id = v_id
  6. UNION ALL
  7. SELECT * FROM (SELECT DISTINCT b_q_id FROM utb) X
  8. CROSS JOIN (SELECT 'R' color, NULL::smallint vol
  9. UNION ALL SELECT 'G', NULL UNION ALL SELECT 'B', NULL) Y
  10. ) X
  11. WHERE b_q_id BETWEEN 12 AND 16
  12. GROUP BY b_q_id, v_color
  13. ORDER BY b_q_id, CASE v_color WHEN 'R' THEN 1 WHEN 'G' THEN 2 ELSE 3 END;$$
  14. ) AS ct(square int, "R" bigint,"G" bigint,"B" bigint)
  15. ORDER BY square;

You probably already start asking yourself whether there is some easier way to do that.

The answer is yes. As it turns out CROSSTAB can be passed another optional parameter a query returning the category list in the same order used for the columns. Then, we can modify our first query as follows to make it yield the correct result:

  1. SELECT * FROM
  2. crosstab(
  3. $$select b_q_id, v_color, SUM(b_vol) qty FROM utb JOIN utv ON b_v_id = v_id
  4. WHERE b_q_id BETWEEN 12 AND 16
  5. GROUP BY b_q_id, v_color
  6. ORDER BY b_q_id, CASE v_color WHEN 'R' THEN 1 WHEN 'G' THEN 2 ELSE 3 END; $$,
  7. $$select 'R' UNION ALL SELECT 'G' UNION ALL SELECT 'B';$$
  8. ) AS ct(square int, "R" bigint,"G" bigint,"B" bigint)
  9. ORDER BY square;

Since PostgreSQL allows the usage of table value constructors, the query

  1. SELECT 'R' UNION ALL SELECT 'G' UNION ALL SELECT 'B';
can be replaced by a shorter one:

  1. VALUES('R'),('G'),('B');

If, when running the query, you get an error message that crosstab is not recognized as a function, it means you dont have the tablefunc module installed. This can be fixed by a simple command (starting with version 9.1)

  1. CREATE EXTENSION IF NOT EXISTS tablefunc;
that has to be executed for a given database. For versions prior to 9.1 you just need to copy the following file: share/contrib/tablefunc.sql to pgadmin, and execute it.

Bookmark and Share
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100