Transpose date columns into rows (PostgreSQL)

Question

I have a table look like this:

    ID    brand    version   stock   date

    1     AA         AAA      50     2019-11-11
    1     AA         AAA      52     2019-11-12
    1     AA         AAA      49     2019-11-13
    ...

    2     BB         BBB      30     2019-11-11
    2     BB         BBB      31     2019-11-12 
    2     BB         BBB      31     2019-11-13
   ...

basically the date column is dynamic, because I extracted the last 14 days from now by using:

  select id, brand, version, stock, date, DATEADD(DAY, -8, getdate()) as date_add
  from myTable 
  where date > date_add

meaning that a week after the dates will be different than now, so my problem is how can I transpose this dynamic table? as I only know the trick to use "case..when", but in this case it doesn't work.

My desired output will be:

ID    brand    version   2019-11-11  2019-11-12  2019-11-13

1     AA         AAA         50         52          49           ...    
2     BB         BBB         30         31          31           ...

I have searched the similar question, but there is only answer for using SQL Server for dynamic way of transpose.

How can I approach this? Thanks!

try this link: [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) — zip, Nov 21 '19 at 16:57
I think I can't use "case..when" in this case as my table is different everyday? — Elaine, Nov 21 '19 at 17:05
Any SQL query should to have **fixed** number of columns. There is no usual way to build its dynamically. Use `json` or `hstore` instead. — Abelisto, Nov 21 '19 at 19:58

score 0 · Answer 1 · answered Nov 21 '19 at 16:55

0

Can be this what you are looking for?

https://www.oracle.com/technical-resources/articles/database/sql-11g-pivot.html

answered Nov 21 '19 at 16:55

jgalaso

22
4

it's similar, but my problem is I can't use a "IN" clause to specify the column I want to transpose to rows. As the dates are different in a week compared to today. – Elaine Nov 21 '19 at 17:00

score 0 · Answer 2 · answered Nov 21 '19 at 18:45

You can use the code below:

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN action = "',
      action,'"  AND ', 
           (CASE WHEN pagecount IS NOT NULL 
           THEN CONCAT("pagecount = ",pagecount) 
           ELSE pagecount IS NULL END),
      ' THEN 1 ELSE 0 end) AS ',
      action, IFNULL(pagecount,'')

    )
  )
INTO @sql
FROM
  t;

SET @sql = CONCAT('SELECT company_name, ', @sql, ' 
                  FROM t 
                   GROUP BY company_name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

It comes from that same link and doesn't use the in command. It build a dynamic string containg the data from the pivoted columns

Transpose date columns into rows (PostgreSQL)

2 Answers2