I am a moderate sql person. But I have come across data sets in my recent queries where I figured out how to convert from a vertical table to a horizontal (pivot view). My issue is I need the column headers on 2/3′rds of them to be a date. Is there a way to populate the column headers dynamically as the date? Here is a sample of what I have created:
DROP TABLE ajDate;
CREATE VOLATILE TABLE ajDate AS (
SELECT ’2013-10-25′ AS date_char,
’00:00:00.000′ AS time_char,
‘-05:00′ AS ofset_char,
CAST(date_char AS DATE) AS dt,
CAST(date_char || ‘ ‘ ||time_char || ofset_char AS TIMESTAMP(3) WITH TIME ZONE) AS tmstp_tz) WITH DATA ON COMMIT PRESERVE ROWS;
SEL
RECSHAREID,
day0,
SUM(DT0) AS DT0_FXSP_PKG_CNT,
SUM(DT1) AS DT1_FXSP_PKG_CNT,
SUM(DT2) AS DT2_FXSP_PKG_CNT,
SUM(DT3) AS DT3_FXSP_PKG_CNT,
SUM(DT4) AS DT4_FXSP_PKG_CNT,
SUM(DT5) AS DT5_FXSP_PKG_CNT,
SUM(DT6) AS DT6_FXSP_PKG_CNT,
SUM(DT7) AS DT7_FXSP_PKG_CNT,
SUM(DT8) AS DT8_FXSP_PKG_CNT,
SUM(DT9) AS DT9_FXSP_PKG_CNT,
SUM(DT10) AS DT10_FXSP_PKG_CNT,
SUM(DT11) AS DT11_FXSP_PKG_CNT,
SUM(DT12) AS DT12_FXSP_PKG_CNT,
SUM(DT13) AS DT13_FXSP_PKG_CNT,
SUM(DT14) AS DT14_FXSP_PKG_CNT,
SUM(DT15) AS DT15_FXSP_PKG_CNT,
SUM(DT16) AS DT16_FXSP_PKG_CNT,
SUM(DT17) AS DT17_FXSP_PKG_CNT,
SUM(DT18) AS DT18_FXSP_PKG_CNT,
SUM(DT19) AS DT19_FXSP_PKG_CNT,
SUM(DT20) AS DT20_FXSP_PKG_CNT,
SUM(DT21) AS DT21_FXSP_PKG_CNT,
SUM(DT22) AS DT22_FXSP_PKG_CNT,
SUM(DT23) AS DT23_FXSP_PKG_CNT,
SUM(DT24) AS DT24_FXSP_PKG_CNT,
SUM(DT25) AS DT25_FXSP_PKG_CNT,
SUM(DT26) AS DT26_FXSP_PKG_CNT,
SUM(DT27) AS DT27_FXSP_PKG_CNT,
SUM(DT28) AS DT28_FXSP_PKG_CNT,
SUM(DT29) AS DT29_FXSP_PKG_CNT,
SUM(DT30) AS DT30_FXSP_PKG_CNT,
SUM(PKGCOUNT) AS FXSP_TOTAL
FROM ( SEL T1.RECSHAREID,
day0,
CASE WHEN T1.DISPATCHDATE = (day0 – INTERVAL ’0′ DAY) THEN FXSP_PKGS ELSE 0 END AS DT0,
CASE WHEN T1.DISPATCHDATE = (day0 – INTERVAL ’1′ DAY) THEN FXSP_PKGS ELSE 0 END AS DT1,
CASE WHEN T1.DISPATCHDATE = (day0 – INTERVAL ’2′ DAY) THEN FXSP_PKGS ELSE 0 END AS DT2,
CASE WHEN T1.DISPATCHDATE = (day0 – INTERVAL ’3′ DAY) THEN FXSP_PKGS ELSE 0 END AS DT3,
CASE WHEN T1.DISPATCHDATE = (day0 – INTERVAL ’4′ DAY) THEN FXSP_PKGS ELSE 0 END AS DT4,
CASE WHEN T1.DISPATCHDATE = (day0 – INTERVAL ’5′ DAY) THEN FXSP_PKGS ELSE 0 END AS DT5,
CASE WHEN T1.DISPATCHDATE = (day0 – INTERVAL ’6′ DAY) THEN FXSP_PKGS ELSE 0 END AS DT6,
CASE WHEN T1.DISPATCHDATE = (day0 – INTERVAL ’7′ DAY) THEN FXSP_PKGS ELSE 0 END AS DT7,
CASE WHEN T1.DISPATCHDATE = (day0 – INTERVAL ’8′ DAY) THEN FXSP_PKGS ELSE 0 END AS DT8,
CASE WHEN T1.DISPATCHDATE = (day0 – INTERVAL ’9′ DAY) THEN FXSP_PKGS ELSE 0 END AS DT9,
CASE WHEN T1.DISPATCHDATE = (day0 – INTERVAL ’10′ DAY) THEN FXSP_PKGS ELSE 0 END AS DT10,
CASE WHEN T1.DISPATCHDATE = (day0 – INTERVAL ’11′ DAY) THEN FXSP_PKGS ELSE 0 END AS DT11,
CASE WHEN T1.DISPATCHDATE = (day0 – INTERVAL ’12′ DAY) THEN FXSP_PKGS ELSE 0 END AS DT12,
CASE WHEN T1.DISPATCHDATE = (day0 – INTERVAL ’13′ DAY) THEN FXSP_PKGS ELSE 0 END AS DT13,
CASE WHEN T1.DISPATCHDATE = (day0 – INTERVAL ’14′ DAY) THEN FXSP_PKGS ELSE 0 END AS DT14,
CASE WHEN T1.DISPATCHDATE = (day0 – INTERVAL ’15′ DAY) THEN FXSP_PKGS ELSE 0 END AS DT15,
CASE WHEN T1.DISPATCHDATE = (day0 – INTERVAL ’16′ DAY) THEN FXSP_PKGS ELSE 0 END AS DT16,
CASE WHEN T1.DISPATCHDATE = (day0 – INTERVAL ’17′ DAY) THEN FXSP_PKGS ELSE 0 END AS DT17,
CASE WHEN T1.DISPATCHDATE = (day0 – INTERVAL ’18′ DAY) THEN FXSP_PKGS ELSE 0 END AS DT18,
CASE WHEN T1.DISPATCHDATE = (day0 – INTERVAL ’19′ DAY) THEN FXSP_PKGS ELSE 0 END AS DT19,
CASE WHEN T1.DISPATCHDATE = (day0 – INTERVAL ’20′ DAY) THEN FXSP_PKGS ELSE 0 END AS DT20,
CASE WHEN T1.DISPATCHDATE = (day0 – INTERVAL ’21′ DAY) THEN FXSP_PKGS ELSE 0 END AS DT21,
CASE WHEN T1.DISPATCHDATE = (day0 – INTERVAL ’22′ DAY) THEN FXSP_PKGS ELSE 0 END AS DT22,
CASE WHEN T1.DISPATCHDATE = (day0 – INTERVAL ’23′ DAY) THEN FXSP_PKGS ELSE 0 END AS DT23,
CASE WHEN T1.DISPATCHDATE = (day0 – INTERVAL ’24′ DAY) THEN FXSP_PKGS ELSE 0 END AS DT24,
CASE WHEN T1.DISPATCHDATE = (day0 – INTERVAL ’25′ DAY) THEN FXSP_PKGS ELSE 0 END AS DT25,
CASE WHEN T1.DISPATCHDATE = (day0 – INTERVAL ’26′ DAY) THEN FXSP_PKGS ELSE 0 END AS DT26,
CASE WHEN T1.DISPATCHDATE = (day0 – INTERVAL ’27′ DAY) THEN FXSP_PKGS ELSE 0 END AS DT27,
CASE WHEN T1.DISPATCHDATE = (day0 – INTERVAL ’28′ DAY) THEN FXSP_PKGS ELSE 0 END AS DT28,
CASE WHEN T1.DISPATCHDATE = (day0 – INTERVAL ’29′ DAY) THEN FXSP_PKGS ELSE 0 END AS DT29,
CASE WHEN T1.DISPATCHDATE = (day0 – INTERVAL ’30′ DAY) THEN FXSP_PKGS ELSE 0 END AS DT30,
FXSP_PKGS AS PKGCOUNT