Pivot for Redshift Database

Pivot a table with Amazon RedShift

You can just create a CASE statement per MetricName but you'll have to use an aggregate as well to make the GROUP BY work.

SELECT dimension_a
      ,dimension_b
      ,MAX(CASE WHEN metric_name = 'm1' THEN metric_value ELSE NULL END) m1
      ,MAX(CASE WHEN metric_name = 'm2' THEN metric_value ELSE NULL END) m2
  FROM my_table
 GROUP BY dimension_a
         ,dimension_b
;

Worth noting that Redshift object names are never case sensitive but column content always is, which is the opposite of SQL Server defaults.

Multiple columns dynamic pivoting in redshift database

For the example you give I have some more general SQL below but this is for the static case you put in your example. Not sure if you are looking for a general / dynamic pivot functionality but if you are you will need to create some sort of SQL generator that takes the column value you want to pivot as input. This generator can be Redshift external code or a stored procedure but cannot be done in basic SQL on Redshift. The code below can be used as a template for use with such a SQL generator.

Set up (you had some decimal values defined as INT):

CREATE TABLE public.temp_car_id 
(
  car_id      VARCHAR(10),
  driver_id   INT,
  salesman    INT,
  tyre_id     INT,
  price_min   decimal(8,2),
  price_max   decimal(8,2),
  price_avg   decimal(8,2)
);

INSERT INTO public.temp_car_id
VALUES
('A',1,1,9,0.61,0.89,0.91),
('A',2,1,9,0.63,0.93,0.58),
('A',3,1,9,0.91,0.83,0.99),
('A',4,1,9,0.53,0.84,0.79),
('A',1,2,7,0.12,0.97,0.87),
('A',2,2,7,0.13,0.30,0.84),
('A',3,2,7,0.17,0.62,0.63),
('A',4,2,7,0.09,0.01,0.19);

SQL code:

select car_id, driver_id,
  min(avg_price_min_s1_t9) as avg_price_min_s1_t9,
  min(avg_price_max_s1_t9) as avg_price_max_s1_t9,
  min(avg_price_avg_s1_t9) as avg_price_mavg_s1_t9,
  min(avg_price_min_s2_t7) as avg_price_min_s2_t7,
  min(avg_price_max_s2_t7) as avg_price_max_s2_t7,
  min(avg_price_avg_s2_t7) as avg_price_mavg_s2_t7
from (
  SELECT car_id,
    driver_id,
    avg(case when tyre_id = 9 and salesman = 1 then price_min end) over( partition by car_id, driver_id, salesman, tyre_id)
        as avg_price_min_s1_t9,
    avg(case when tyre_id = 9 and salesman = 1 then price_max end) over( partition by car_id, driver_id, salesman, tyre_id)
        as avg_price_max_s1_t9,
    avg(case when tyre_id = 9 and salesman = 1 then price_avg end) over( partition by car_id, driver_id, salesman, tyre_id)
        as avg_price_avg_s1_t9,
    avg(case when tyre_id = 7 and salesman = 2 then price_min end) over( partition by car_id, driver_id, salesman, tyre_id)
        as avg_price_min_s2_t7,
    avg(case when tyre_id = 7 and salesman = 2 then price_max end) over( partition by car_id, driver_id, salesman, tyre_id)
        as avg_price_max_s2_t7,
    avg(case when tyre_id = 7 and salesman = 2 then price_avg end) over( partition by car_id, driver_id, salesman, tyre_id)
        as avg_price_avg_s2_t7
  FROM public.temp_car_id ) a
group by 1,2
order by 1,2;

It is unclear what aggregate function you want to use for combining these values as you only have 1 value per type so I used avg().

Redshift - Unsupported PIVOT column type: text

The column reminder_type was a result of REGEXP_REPLACE that resulted in type VARCHAR(101).

Suddenly it worked when I explicitly cast the column to VARCHAR

REGEXP_REPLACE(remin_type, '<regex>', '') AS reminder_type doesn't work

REGEXP_REPLACE(remin_type, '<regex>', '')::VARCHAR AS reminder_type works perfectly

Pivot in Redshift

Just use conditional aggregation:

select pn,
       max(case when seqnum = 1 then gender end) as primary_gender,
       max(case when seqnum = 2 then gender end) as joint_gender,
       max(case when seqnum = 1 then dob end) as primary_dob,
       max(case when seqnum = 2 then dob end) as joint_dob
from (select t.*, row_number() over (partition by pn order by pi) as seqnum
      from t
     ) t
group by pn

MS-Excel Power Pivot + Amazon Redshift: possible to connect the data source?

Official Amazon Redshift ODBC drivers produce the same error for me but it works well with postgresql drivers which can be downloaded from here: http://www.postgresql.org/ftp/odbc/versions/msi/

Pivot and Sum in Amazon Redshift

This looks to be a pivot query. I think this does what you are looking for:

create table table1 (id int, name varchar(16));
insert into table1 values
(1, 'A'),
(3, 'B')
;

create table table2 (id int, label varchar(16), value varchar(16));
insert into table2 values 
(1,   'tag', 'a'),
(1,   'tag', 'b'),
(1,   'time', '10'),
(1,   'score', '20'),
(2,   'tag', 'a'),
(2,   'time', '30'),
(2,   'score', '40'),
(3,   'tag', 'b'), 
(3,   'time', '50'),
(3,   'score', '60')
;

select t2.id, a.name, a.tag_value, sum(decode(label, 'time', value::int)) as total_time, sum(decode(label, 'score', value::int)) as total_score
from table2 t2
join (
    select id, name, value as tag_value
    from table1 t1 left join table2 t2 using(id)
    where t2.label in ('tag')
    ) a
on t2.id = a.id 
group by 1, 2, 3
order by 1, 2, 3
;

---

Related Topics