Login/Register
Postgresql transpose rows to columns
Asked Answered
sqldatabasepostgresqlpivotpivot-table
P

3

8

I have this query that yields a total of 1 million rows similar to the example extract shown bellow

SELECT * 
FROM sales
shop date hour row_no amount
shop_1 2012-08-14 00:08:00 P01 10
shop_2 2012-08-12 00:12:00 O05 40
shop_2 2012-08-12 00:12:00 A01 20

I can do this and get these results

SELECT shop, SUM(amount) 
FROM sales 
GROUP by shop
shop amount
shop_1 5666
shop_2 4044
shop_3 4044

However I would like to place the dates as column attributes (As shown in the example bellow), and I'm unsure as to how to do it.

shop 2012-08-1 2012-08-2 2012-08-3
shop_1 4005 5667 9987
shop_2 4333 4554 1234
shop_3 4555 6778 6677

I would like to group the results by store in the rows, and group by days in the columns

Purdah answered 21/4, 2017 at 14:4 Comment(4)
Search for crosstab – Disappear
He heard some crosstab but did anyone know how to use it? – Purdah
Cross tab requires you to know the dimensions of the table produced. You won't know that unless you know how many days you've got or want. Do you? Do you want every day represented or just a week, because "every day" requires dynamic sql AND crosstab. – Repine
Also check github.com/hnsl/colpivot which can discover the columns – Dotti
M
0

First, you must install tablefunc extension. Since version 9.1 you can do it using create extension:

CREATE EXTENSION tablefunc;

select * from crosstab (  
    select shop, date, SUM(amount) 
    from sales 
    group by shop

    'select date from sales order by 1') 
AS ct(shop: text,  '2012-08-1' text, '2012-08-2' text, '2012-08-3' text)
Maneuver answered 22/4, 2017 at 6:45 Comment(0)
W
0

You can use crosstab to the achieve the desired results, here is a solution:

SELECT * FROM CROSSTAB(
  'SELECT shop, date, amount FROM sales ORDER BY 1,2',
  'SELECT DISTINCT date FROM sales ORDER BY 1'
) AS ct(shop text, "2012-08-12" int, "2012-08-14" int);

You can also check here: DbFiddle

Explanation: Crosstab function takes two arguments, a source query and a category query. Source query provides the data to be pivoted and category query provides the column names for pivot table. You can checkout the official documentation here: tableFunc.

crosstab ( source_sql text, category_sql text ) → setof record Produces a “pivot table” with the value columns specified by a second query.

Wreck answered 15/7, 2023 at 15:39 Comment(0)
O
0

While the 2 answers show the basic crosstab features, with PL/pgSQL you can do a lot more. You mentioned having 1 million rows in your table and I'm assuming quite a lot of unique dates in those rows that need to be specified by hand for crosstab. A dynamic query like the one bellow will help you with that

CREATE EXTENSION IF NOT EXISTS tablefunc;
DROP TABLE IF EXISTS results, distinct_dates, distinct_shops;

/*Pseudo CTEs*/
CREATE TEMP TABLE distinct_dates (distinct_date) AS (
  SELECT DISTINCT date
  FROM sales 
  ORDER BY date
);

CREATE TEMP TABLE distinct_shops ( distinct_shop ) AS (
  SELECT DISTINCT shop 
  FROM sales
  ORDER BY shop
);

DO $main_plpgsql$
  DECLARE dates_var TEXT := (
      SELECT string_agg(format('%I', distinct_date::text), ' numeric, ')
      FROM distinct_dates 
    );

  BEGIN 
  EXECUTE format('
    CREATE TEMP TABLE results AS (
      SELECT *
      FROM crosstab($crosstab_string$
        SELECT *, (
          SELECT SUM(amount)
          FROM sales 
          WHERE (shop, date) = (distinct_shop, distinct_date)
        )
        FROM distinct_shops CROSS JOIN distinct_dates
        ORDER BY distinct_shop, distinct_date;
      $crosstab_string$) AS ct(shops text, %s numeric)
    )', dates_var);

  END
$main_plpgsql$;

SELECT * FROM results;

The idea of the query is simple, generate a table from a crosstab query being determined by the distinct dates present at runtime

Why generate a results table?

  • Crosstab returns setof record meaning that we need to provide a definition list (which we don't have in advance), we use PL/pgSQL for that but due the way PL/pgSQL works we can't simply output the results, and we can't return a table since we don't know the crosstab structure in advance, so instead we generate a table that has it's structure determined at run time

How do we specify the structure of the table

  • PL/pgSQL permits dynamic queries using the EXECUTE keyword (distinct from the SQL keyword), it takes a string and well executes it, we can format and thus inject dynamic vars in the string and in our results table's definition list

  • The distinct dates can be found with a query such as

SELECT DISTINCT date AS distinct_date 
FROM sales 
ORDER BY date

we aggregate the results into a string to be injected in the EXECUTE query, we want to specify the name and type of the attributes, the general form is

atr_name1 datatype1, atr_name2 datatype2 [, ...]

we can compute the dynamic part with this query as a variable (in our case dates_var)

  DECLARE dates_var TEXT := (
      SELECT string_agg(format('%I', distinct_date::text), ' numeric, ')
      FROM distinct_dates 
    );

Here is an extract of what this string might look like, (in a million row query it is almost always going to wayyyy bigger)

"2012-01-01" numeric, "2012-01-04" numeric, "2012-01-05"

From there we can just define a execute statement that creates a results table as shown in the code sample

An example of this query can be seen here, while OP didn't provide the full dataset (understandably) using a random simplified dataset we can see an example result, for this dataset shown in the link, we end up with this table

shops 2012-01-01 2012-01-02 2012-01-03 2012-01-04 2012-01-05 2012-01-06 2012-01-07 2012-01-08 2012-01-09 2012-01-10 2012-01-11 2012-01-12 2012-01-13 2012-01-14 2012-01-15 2012-01-16 2012-01-17 2012-01-18 2012-01-19 2012-01-20 2012-01-21 2012-01-22 2012-01-23 2012-01-24 2012-01-25 2012-01-26 2012-01-27 2012-01-28 2012-01-29 2012-02-01 2012-02-02 2012-02-03 2012-02-04 2012-02-05 2012-02-06 2012-02-07 2012-02-08 2012-02-09 2012-02-10 2012-02-11 2012-02-12 2012-02-13 2012-02-14 2012-02-15 2012-02-16 2012-02-17 2012-02-18 2012-02-19 2012-02-20 2012-02-21 2012-02-22 2012-02-23 2012-02-24 2012-02-25 2012-02-26 2012-02-27 2012-02-28 2012-03-01 2012-03-02 2012-03-03 2012-03-04 2012-03-05 2012-03-06 2012-03-07 2012-03-08 2012-03-09 2012-03-10 2012-03-12 2012-03-13 2012-03-14 2012-03-15 2012-03-16 2012-03-17 2012-03-18 2012-03-19 2012-03-20 2012-03-21 2012-03-22 2012-03-23 2012-03-24 2012-03-25 2012-03-26 2012-03-27 2012-03-28 2012-04-01 2012-04-02 2012-04-03 2012-04-04 2012-04-05 2012-04-06 2012-04-07 2012-04-08 2012-04-09 2012-04-10 2012-04-11 2012-04-12 2012-04-13 2012-04-14 2012-04-15 2012-04-16 2012-04-17 2012-04-18 2012-04-19 2012-04-20 2012-04-22 2012-04-23 2012-04-25 2012-04-26 2012-04-27 2012-04-28 2012-05-01 2012-05-02 2012-05-03 2012-05-04 2012-05-05 2012-05-06 2012-05-07 2012-05-09 2012-05-10 2012-05-11 2012-05-12 2012-05-13 2012-05-14 2012-05-15 2012-05-16 2012-05-17 2012-05-18 2012-05-19 2012-05-20 2012-05-21 2012-05-22 2012-05-23 2012-05-24 2012-05-25 2012-05-26 2012-05-27 2012-05-28 2012-06-01 2012-06-03 2012-06-04 2012-06-05 2012-06-06 2012-06-07 2012-06-08 2012-06-09 2012-06-10 2012-06-11 2012-06-12 2012-06-13 2012-06-14 2012-06-15 2012-06-16 2012-06-17 2012-06-18 2012-06-19 2012-06-20 2012-06-21 2012-06-22 2012-06-23 2012-06-24 2012-06-25 2012-06-26 2012-06-27 2012-06-28 2012-07-01 2012-07-02 2012-07-03 2012-07-04 2012-07-05 2012-07-06 2012-07-07 2012-07-08 2012-07-09 2012-07-10 2012-07-11 2012-07-12 2012-07-13 2012-07-14 2012-07-15 2012-07-16 2012-07-17 2012-07-18 2012-07-19 2012-07-20 2012-07-21 2012-07-22 2012-07-23 2012-07-24 2012-07-25 2012-07-26 2012-07-27 2012-07-28 2012-08-01 2012-08-02 2012-08-03 2012-08-04 2012-08-05 2012-08-06 2012-08-07 2012-08-08 2012-08-09 2012-08-10 2012-08-11 2012-08-12 2012-08-13 2012-08-14 2012-08-15 2012-08-16 2012-08-17 2012-08-18 2012-08-19 2012-08-20 2012-08-21 2012-08-22 2012-08-23 2012-08-24 2012-08-25 2012-08-26 2012-08-27 2012-08-28 2012-09-01 2012-09-02 2012-09-03 2012-09-05 2012-09-06 2012-09-07 2012-09-08 2012-09-09 2012-09-10 2012-09-11 2012-09-12 2012-09-13 2012-09-14 2012-09-15 2012-09-16 2012-09-17 2012-09-18 2012-09-19 2012-09-20 2012-09-21 2012-09-22 2012-09-23 2012-09-24 2012-09-25 2012-09-26 2012-09-27 2012-09-28 2012-09-29 2012-10-01 2012-10-02 2012-10-03 2012-10-04 2012-10-05 2012-10-06 2012-10-07 2012-10-08 2012-10-09 2012-10-10 2012-10-11 2012-10-12 2012-10-13 2012-10-14 2012-10-15 2012-10-16 2012-10-17 2012-10-19 2012-10-20 2012-10-21 2012-10-22 2012-10-23 2012-10-24 2012-10-25 2012-10-26 2012-10-27 2012-10-28 2012-11-01 2012-11-02 2012-11-03 2012-11-04 2012-11-05 2012-11-06 2012-11-07 2012-11-08 2012-11-09 2012-11-10 2012-11-11 2012-11-12 2012-11-13 2012-11-14 2012-11-15 2012-11-16 2012-11-17 2012-11-18 2012-11-19 2012-11-20 2012-11-21 2012-11-22 2012-11-23 2012-11-24 2012-11-25 2012-11-26 2012-11-27 2012-11-28
shop_1 11670 4058 8734 11458 3880 514 17746 14248 null null 17990 null null null null 5183 8486 13051 10117 9337 6270 6060 null null null 1045 15167 16552 null null 7541 null null 15928 null null null 3656 5899 null 7054 598 null 9624 10924 null null 14426 null 7047 null null null null null 10820 631 9660 null null 5020 4838 null 4033 3362 null null null 5876 6776 null null null 8894 null 4258 null 10430 6327 null 6205 2152 null 387 17246 5310 8541 7602 5848 690 17564 8042 null null null 4121 null 3926 null null null null null null null null 12495 null null null null null null null null 347 null null null null null null null null null 2070 null null 3244 null 2028 null null 900 null null 8306 5389 null null null null 18962 null null null 812 null 6891 null null 8891 null 7881 null null null 1679 null null null 8341 null null 9881 3748 1657 8524 null null 6785 null 2864 9673 null null null 2052 4988 null 9965 null 5002 1756 4602 3014 10261 null 9530 null null null 82 6 2435 null 159 null 8962 null null null null null 14402 10949 null 2800 6307 1097 null null null 2825 null 7268 6223 null null 8715 1757 null 9235 null 7702 null null 13122 null null null null null 1423 3226 null 7428 7021 6558 null null null null null null 8386 null 1467 14031 7515 null null null null 5275 6367 5714 6998 null null 6444 null 1318 275 1316 null null null null 4715 null 9158 null 11397 null null 9107 null 9264 9592 null 358 null null 3818 774 null null null 7194 null 5251 null null null null null 9389 14153 2157 5151 null null
shop_2 33641 6668 null 8052 2705 12434 33 7063 20184 null 3354 14079 2695 7910 null 3740 4509 1605 null null null 17961 14613 3535 1350 7421 2347 4728 null null null null 8653 null null null 1638 null 5440 null 6947 null null 877 10575 null 9606 16486 8634 9043 3864 null null null null 9524 null null null null 3803 12995 null 7559 1924 4737 null null null null 7091 3264 9106 2983 9348 21264 6879 null 8462 7606 869 null null null 4268 13323 7855 null 12398 null null null null null 2863 6555 null 7853 8013 9811 3622 null 5450 null null 9617 18687 4377 null 6994 8506 1941 364 null 3185 7093 null null 8749 null 3909 null null null null null 13663 6997 null null null null null null 8203 10964 8172 5212 null null null 3050 20464 5124 null null null null null null null 2124 null null null 1355 null 3689 3820 6769 null null null null 10232 1599 1394 214 null null null 5584 null null 8250 null 7517 null null 394 null 14322 2642 null 11048 7268 null null null 2690 null 1494 6205 null null 3587 9591 null null null null 4662 5679 9306 null 1399 null 9896 null 9485 null 915 null 2674 null null null null null null 4899 3818 5468 8289 null 10017 12986 448 1531 null null 6156 null null null null null 9349 null 18586 null null 7410 6477 6061 765 null null 21686 1978 1345 null 4150 5192 3102 null 1706 null null null null 3244 null 4272 21924 7226 null null null 8768 null null 1411 null null 1831 null 732 null null null 8033 10825 8213 null 3746 4865 null 2025 null 5106 null null null 8077 null null null 4549 4229 6379 16469 null
shop_3 11897 5402 11165 2659 3463 null 7857 448 4423 null 2957 10177 11238 1481 null 15229 10298 755 10026 null 9837 8688 2713 5081 4281 1308 9582 20216 null null null null 4263 null null null 14804 null null null null null null null null null null 17377 null null null 7599 6273 8464 3576 2296 1899 null null null null null null null 18974 5838 null 6847 null null null null null null null null 7568 23077 null 8203 16670 null null null 635 9152 8586 null null null 4839 null null null null null 2155 8478 null null null 103 4871 null null 15 null null 6971 null 13996 null 6918 8732 null null 8730 12233 4020 null null null null null 4228 null 2665 null null null null 3443 null 6611 null null 3565 1603 4107 484 null 5025 null null 7156 null null 6163 null 194 null null null null 6602 4605 null 3217 2432 null 1891 1898 null 1319 12953 null 8726 4068 4021 null null null 4684 null 9550 null null null 9685 null 3581 null 9702 11273 1129 null 3436 9104 2061 5553 null null null null 1352 null null null 5241 null 4244 2091 null null 2165 6119 5481 null null null 5121 null null 52 8303 null 12789 null null 5688 18641 null 2415 5395 10265 null null 4956 null 1405 3394 6041 104 null null 15 null 4850 15913 null 2047 null 12728 544 null 14219 7648 null 12659 null 6283 null 1693 5033 null 2442 null null 10641 9841 14013 null null null null 270 7849 8318 null 2707 8670 null 2465 null null 6455 4232 1259 4675 3465 null 9112 4715 7001 null 273 null 2241 null null null 12171 null 8181 null 12413 2200 2430 7085 2766 null 5561 10780
shop_4 2111 null 6062 13144 231 9654 null null 5468 null 9045 14083 9744 8299 null 9636 null 11331 null 16015 null 3781 8668 8795 15603 8392 1515 13049 4856 7761 null 6051 null null 2876 12478 2659 8666 null 862 null null 7471 4915 null null null 2026 674 2665 null null null null 3096 null null 17993 11255 null null null null null null 6635 3112 9977 null 3633 null null 1773 1019 1148 null 12087 6896 666 8342 null 3111 null null 3952 6000 null 6546 null 6520 null 13080 9470 8122 9929 null 33576 null null null null 5431 null null 8574 6875 null null null null 3808 null null 3906 null null null null null null 4747 null 1616 1238 1056 null null 9675 null 1268 7622 1813 6539 null 7750 6250 null 3712 null null null 256 4761 14541 null null 5523 null 2273 null null null 315 null 9857 7576 9196 null null null null 2219 7480 null 1215 5560 null null null 2423 null null null null null null 154 null 13504 7107 11755 null 17838 13819 null 7392 null null null null null 2820 2274 null 11877 6010 2122 null null 3891 8685 null 4399 7091 7841 8232 null 398 null null null 9781 null null 8293 null 6521 null null null 8041 null null null 18767 null 521 null 7210 4673 2339 null 2929 null null null null null null null null null null null null null 5379 null 6801 null null 6936 null 7144 null null null null 4582 1341 null 431 null 12434 null 7450 2660 null null null null null null 4164 4655 6323 null 15240 null null null 8477 null null null null 7773 null 5451 null 15849 14017 null 8647 null null null 11104 13735 null null null null
shop_5 null 18360 13170 4599 null 6793 13896 4713 null 6816 null null 1850 1696 4972 4931 null null 656 22731 4828 7145 371 null null 8981 10282 10219 null null 7311 null null null 3268 6235 8284 3855 null 7161 null null null 7281 null null 8341 5949 null null 6516 null null 7832 null null null null null 8884 7048 4161 9100 null 9026 null null 8560 6350 8346 2573 null null null null 25260 8476 9215 2524 null null 1782 null null 1266 8258 6971 null null 8810 null null 8751 null 2275 null 3234 null null 10315 null 2931 1036 1049 13078 9052 3684 9223 null null null null null null null 134 null null 7778 6704 null 7506 null null 1631 5568 3507 null 4907 15688 null null 3309 null 3472 388 2564 null null 9734 7821 8761 null 5350 null 13149 null null null 9653 null null null 7049 2189 null null null null 536 17672 null 8192 null 14305 6016 null null null 1871 1342 null null 17732 null 5750 null null 9622 2048 null 4791 6957 null null null null null null 5460 13050 null 3196 null null null null null 7964 24706 null 107 6905 null 8225 null null null null null 4317 128 167 null 1272 null null 8959 1262 null 12446 6051 null 6461 1170 14089 1223 null null null null null 7616 null null 10399 null 1903 null null null 5777 6515 null 9081 null 2457 null 22522 null 9929 null 8573 817 null null null 5789 4054 5097 null null 4248 5914 null null 14740 null null 2272 null 2797 13007 null 15756 3869 null 8878 null 9282 null null null 8127 6258 null 2429 null 8216 null 7693 9692 null 752 null 8639 null null 9432 9859 null null null
shop_6 3531 11984 5045 11911 1335 12275 null 2535 null 8852 7183 null 7840 null null 3395 4355 null null 2666 5947 null 8904 6099 null 12419 1038 5016 null 8905 8981 null 2141 null null null 9203 null null 1702 null 6102 null null 5771 10453 9214 12435 1793 13255 null 6821 null null null null null 3767 null null 9148 3658 3116 null null null null 7643 8439 9466 11798 null 2968 null 29 5660 5899 null 9311 4435 13012 9012 13688 null 1610 3697 4384 7904 9816 null null null null 9173 null 7998 8171 5946 15150 null 4537 null 4787 null null null 8071 null null null null 9198 null 1193 null 1851 null null 5334 2828 null null null 8084 9264 1272 4066 6947 null null null 6468 4527 null 7592 null null 9902 null 712 11458 null 702 3182 null null 9057 null null 9416 4140 null null null 2102 null 12284 null null null 7007 1449 6243 6537 null 2455 null null 3671 9945 null 4719 1876 null 2390 9306 null null null 2862 null 1856 null null 7722 4096 null null 9718 null 7042 null 1193 null null 9235 10708 464 null null 16267 null 2976 4984 8942 null null null null 1737 null 19845 5110 null null 7734 4818 3787 9020 5565 5304 5586 null 3167 null null null 9335 null null 5307 4926 null null 6888 null 5848 null 1327 3990 null null null null null null null null 16160 7382 null 997 4498 null null null null null null 6435 6929 7210 null 7241 7982 null null null 4439 null null 4534 null null 11767 null 6536 4617 null null 4043 null null 3942 null 4474 510 9930 4217 null null null 8832 null 9809 1779 8141 4263 2510 15749 null 16162 null

Other cool stuff we can do. For example this query will find all sales for each shop for each day in January 2012

CREATE EXTENSION IF NOT EXISTS tablefunc;
DROP TABLE IF EXISTS results, dates, distinct_shops;

CREATE TEMP TABLE dates (distinct_date) AS (
   SELECT generate_series(
     '2012-01-01'::date, '2012-01-30'::date, '1 day'::interval
   )
);

CREATE TEMP TABLE distinct_shops ( distinct_shop ) AS (
  SELECT DISTINCT shop 
  FROM sales
  ORDER BY shop
);

DO $main_plpgsql$
  DECLARE dates_var TEXT := ( 
      SELECT string_agg(format('%I', distinct_date::text), ' numeric, ') 
      FROM dates
   );

  BEGIN   
  EXECUTE format('
    CREATE TEMP TABLE results AS (
      SELECT *
      FROM crosstab($crosstab_string$
        SELECT *, (
          SELECT SUM(amount)
          FROM sales 
          WHERE (shop, date) = (distinct_shop, distinct_date)
        )
        FROM distinct_shops CROSS JOIN dates
        ORDER BY distinct_shop, distinct_date;
      $crosstab_string$) AS ct(shops text, %s numeric)
    )', dates_var);

  END
$main_plpgsql$;

SELECT * FROM results;

In fact there is a lot more that you can do, for example you could see the amount on a certain range or for a season/year, you could add totals for each shop as another entry besides the dates, or totals for each date. But for the sake of simplicity this will be left as an exercise for the reader. A Full example can be seen in the link above or here

Again, keep in mind that I have removed some attributes for the sake of simplicity

Optimal answered 16/4 at 6:50 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.