Pivoting in SQL Not working

[Metabase8080]

I am new to Metabase but posted back in November about needing to email a pivoted table

Subscription Email of Pivoted Table

Hello, I have below problem statement. I have created a table as follows which filters data to be from the last 7 days (Important as date window shifts +1 date every day) TABLE 1 Name | Date | Value A | 2021-05-26 20:39:15.3366667 | 2 B | 2021-05-26 17:32:15.3366667 | 1 C | 2021-05-26 21:37:15.3366667 | 4 D | 2021-05-26 15:38:11.3366667 | 10 A | 2021-05-26 20:29:15.3366667 | 12 B | 2021-05-26 17:35:15.3366667 | 22 C | 2021-05-26 21:17:15.3366667 | 12 D | 2021-05-26 15:31:11.3366667 |…

According to @flamber I need to pivoting in SQL and just use a regular Table visualization.

I attempted to write this SQL query and I can get the initial table but when I go to Pivot it in SQL i get a variety of syntax errors.

Current Query

SELECT * FROM ( 
SELECT "source"."Full Name" AS "Full Name", CAST("source"."Sessions__start_time_2" AS date) AS "Sessions__start_time", (CAST(sum("source"."Sessions__duration_sec") AS float) / CASE WHEN 3600.0 = 0 THEN NULL ELSE 3600.0 END) AS "DailyUsage"
FROM (SELECT "public"."workers"."id" AS "id", "public"."workers"."department_id" AS "department_id", "public"."workers"."first_name" AS "first_name", "public"."workers"."last_name" AS "last_name", "public"."workers"."employee_id" AS "employee_id", "public"."workers"."created_at" AS "created_at", "public"."workers"."updated_at" AS "updated_at", "public"."workers"."deleted_at" AS "deleted_at", "public"."workers"."assignable" AS "assignable", concat("public"."workers"."first_name", ' ', "public"."workers"."last_name") AS "Full Name", "Sessions"."id" AS "Sessions__id", "Sessions"."session_uuid" AS "Sessions__session_uuid", "Sessions"."device_id" AS "Sessions__device_id", "Sessions"."status" AS "Sessions__status", "Sessions"."duration_sec" AS "Sessions__duration_sec", "Sessions"."start_time" AS "Sessions__start_time", "Sessions"."worker_id" AS "Sessions__worker_id", CAST("Sessions"."start_time" AS date) AS "Sessions__start_time_2" FROM "public"."workers"
LEFT JOIN "public"."sessions" "Sessions" ON "public"."workers"."id" = "Sessions"."worker_id") "source"
GROUP BY "source"."Full Name", CAST("source"."Sessions__start_time_2" AS date)
ORDER BY "source"."Full Name" ASC, CAST("source"."Sessions__start_time_2" AS date) ASC
)
PIVOT( "Daily Usage" FOR "Sessions__start_time_2" IN ("November 21, 2022", "November 23, 2022")) AS PIVOT_TABLE

Output: ERROR: syntax error at or near "FOR" Position: 0

I can't find any documentation related to Metabase supporting pivot in SQL queries. Again i need to pivot in SQL so i can email the table and cannot pivot outside of SQL.

Thanks in advance for your help!

[TonyC]

What database are you using?

[Metabase8080]

PostgreSQL

[TonyC]

To my knowledge of PostgreSQL there is no PIVOT function the way you are sing it in the SQL query ... Are you abele to run that query directly on PostgresQL?