Dynamic column naming in Netezza

Question

Is there any way to dynamically name pivoted columns in Netezza? In SSMS, I'd use a stored procedure, but our new Netezza box restricts SP creation. Using CASE statements, I can use the row number to pivot daily sums by month, but I'd like the new column names to be the actual arbitrary MONTHID rather than the row numbers (MONTH_ID1 contains ROW_NUMBER 1-12 and a corresponding MONTHID). I'm thinking strategically placed quotations and a subquery, for instance--but I can't get it to work.

select "DAY"
,sum(case when "ROW_NUMBER" = 1 then SUMOFCHGS else null end) as "1"
,sum(case when "ROW_NUMBER" = 2 then SUMOFCHGS else null end) as "2"
,sum(case when "ROW_NUMBER" = 3 then SUMOFCHGS else null end) as "3"
,sum(case when "ROW_NUMBER" = 4 then SUMOFCHGS else null end) as "4"
,sum(case when "ROW_NUMBER" = 5 then SUMOFCHGS else null end) as "5"
,sum(case when "ROW_NUMBER" = 6 then SUMOFCHGS else null end) as "6"
,sum(case when "ROW_NUMBER" = 7 then SUMOFCHGS else null end) as "7"
,sum(case when "ROW_NUMBER" = 8 then SUMOFCHGS else null end) as "8"
,sum(case when "ROW_NUMBER" = 9 then SUMOFCHGS else null end) as "9"
,sum(case when "ROW_NUMBER" = 10 then SUMOFCHGS else null end) as "10"
,sum(case when "ROW_NUMBER" = 11 then SUMOFCHGS else null end) as "11"
,sum(case when "ROW_NUMBER" = 12 then SUMOFCHGS else null end) as "12"
FROM DLYAMTS D
    JOIN MONTH_ID1 M ON D.MONTHID = M.MONTHID
GROUP BY 1
ORDER BY 1

I can envision doing this in the future with a variable number of columns, but I'm just looking for a year's worth of month columns for now.

score 1 · Answer 1 · answered Dec 08 '16 at 06:18

If you wish to create a sp that returns a 'table object' I have the same understanding: not possible. At our shop we have however created sp's that crate a (permanent) table based on a set of arguments. One of those arguments is the 'target table name'. Others arguments in your case: the 'input table name', the 'group by columns', a 'where clause', the 'organize on' columns, and the 'distribute on' columns.

I hope this helps :)

Lars

Dynamic column naming in Netezza

1 Answers1