Netezza Pivot Rows to Column With Example

  • Post author:
  • Post last modified:September 11, 2019
  • Post category:General
  • Reading time:3 mins read

There may be a situation you may want PIVOT rows to column and vice versa. Netezza don’t have PIVOT function like other RDBMS. Netezza Pivot rows to column and column to row requires the CASE or DECODE statements.

Netezza PIVOT Rows to Column

Let us consider the below input data

TRAINING.ADMIN(ADMIN)=> select * from students; 
 CLASS | SECTION | PASS_FAIL | COUNT 
--------+---------+-----------+------- 
 CLASS2 | B | FAIL | 333 
 CLASS2 | B | PASS | 543 
 CLASS1 | A | FAIL | 123 
 CLASS1 | A | PASS | 321 
(4 rows)

Now we want to convert rows to column

Below is the query that you can use to Netezza pivot rows to column

SELECT 
 CLASS, 
 SECTION, 
 SUM(CASE WHEN PASS_FAIL = 'PASS' THEN COUNT ELSE 0 END) as PASS, 
 SUM(CASE WHEN PASS_FAIL = 'FAIL' THEN COUNT ELSE 0 END) as FAIL 
FROM STUDENTS 
GROUP BY 1,2;
TRAINING.ADMIN(ADMIN)-> 

CLASS | SECTION | PASS | FAIL 
--------+---------+------+------ 
 CLASS2 | B | 543 | 333 
 CLASS1 | A | 321 | 123 
(2 rows)

Netezza PIVOT Column to Rows

Let us consider the below input data

TRAINING.ADMIN(ADMIN)=> select * from sample_sales; 
 MONTH_ID | SALE_QTY 
----------+---------- 
 201601 | 897456 
 201603 | 267156 
 201602 | 232253 
 201604 | 265646 
 201606 | 265654 
 201606 | 123457 
(6 rows)

If you want to convert column to rows. Use below query:

select 
sum(case when month_id = 201601 then sale_qty else null end) as "201601", 
sum(case when month_id = 201602 then sale_qty else null end) as "201602", 
sum(case when month_id = 201603 then sale_qty else null end) as "201603", 
sum(case when month_id = 201604 then sale_qty else null end) as "201604", 
sum(case when month_id = 201605 then sale_qty else null end) as "201605", 
sum(case when month_id = 201606 then sale_qty else null end) as "201606" 
from sample_sales;

TRAINING.ADMIN(ADMIN)->  
 201601 | 201602 | 201603 | 201604 | 201605 | 201606 
--------+--------+--------+--------+---------+-------- 
 897456 | 232253 | 267156 | 265646 | 1268957 | 389111 
(1 row)

Read:

Tags: ,

This Post Has One Comment

  1. Matt December 7, 2016

    Thank you so much for this. Next, is there any way to dynamically name the new columns? In SSMS, I’d use a stored procedure, but our Netezza box restricts SP creation.

    My Example:
    Using CASE statements, I can use the row number to pivot daily amounts by month, but I’d like the new column names to be the actual MONTHID rather than the row numbers (MONTH_ID1 contains ROW_NUMBER 1-12 and a corresponding MONTHID). There must be some way to do it–strategically place quotations and a subquery, for instance–but I can’t find it.

    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

Comments are closed.