1

I have a very simple table like below

Events:

Event_name Event_time
A 2022-02-10
B 2022-05-11
C 2022-07-17
D 2022-10-20

To a table like this are added new events, but we always take the event from the last X days (for example, 30 days), so the query result for this table is changeable.

I would like to transform the above table into this:

A B C D
2022-02-10 2022-05-11 2022-07-17 2022-10-20

In general, the number of columns won't be constant. But if it's not possible we can add a limitation for the number of columns- for example, 10 columns.

I tried with crosstab, but I had to add the column name manually this is not what I mean and it doesn't work with the CTE query

WITH CTE AS (
SELECT DISTINCT
    1 AS "Id",
    event_time,
    event_name,
    ROW_NUMBER() OVER(ORDER BY event_time) AS nr
FROM 
    events
WHERE
    event_time >= CURRENT_DATE - INTERVAL '31 days')
SELECT *
FROM
    crosstab (
    'SELECT id, event_name, event_time
        FROM
            CTE
        WHERE
            nr <= 10
        ORDER BY 
            nr') AS ct(id int, 
                      event_name text,
                      EventTime1 timestamp,
                      EventTime2 timestamp,
                      EventTime3 timestamp,
                      EventTime4 timestamp,
                      EventTime5 timestamp,
                      EventTime6 timestamp,
                      EventTime7 timestamp,
                      EventTime8 timestamp,
                      EventTime9 timestamp,
                      EventTime10 timestamp)

This query will be used as the data source in Tableau (data visualization and analysis software) it would be great if it could be one query (without temp tables, adding new functions, etc.)

Thanks!

patryska
  • 11
  • 2
  • Have you considered [pivoting already in Tableau](https://help.tableau.com/current/pro/desktop/en-us/pivot.htm)? – Zegarek Nov 07 '22 at 19:32
  • Related threads: [1](https://stackoverflow.com/q/23929707/5298879), [2](https://stackoverflow.com/q/11740256/5298879), [3](https://stackoverflow.com/q/74246748/5298879). It boils down to the fact you're dealing with a changing structure, so either your select needs to keep changing along with it (specifying column names and types `AS ct(...)`), or you need to build a function that'll materialize it before your select. – Zegarek Nov 07 '22 at 19:40
  • You could use [initial query](https://help.tableau.com/current/pro/desktop/en-us/connect_basic_initialsql.htm) to run a function with dynamic sql [`execute format`](https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN) to build and populate your output table under a predefined name, then use that table as the source. – Zegarek Nov 07 '22 at 19:47

0 Answers0