I am new to Metabase but posted back in November about needing to email a pivoted table
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!