Redshift で SPLIT_TO_ARRAY 関数でデリミタ区切りのリストを横縦変換する

• テーブルのフィールドにカンマ区切りのデータを登録する。

dev=# create table pivot_test (id int, txt varchar);
CREATE TABLE
dev=# insert into pivot_test values (1,'AZ, Sidmar,1'),(2,'Kaihatsu,Suarez,9'),(3,'Azuma,Neymar,11'),(4,'OZ,Iniesta,8');
INSERT 0 4
dev=# select * from pivot_test;
 id | txt
----+-------------------
 1  | AZ, Sidmar,1
 2  | Kaihatsu,Suarez,9
 3  | Azuma,Neymar,11
 4  | OZ,Iniesta,8

• Using SPLIT_TO_ARRAY to change one varchar column to array

 dev=# select id,SPLIT_TO_ARRAY(txt,',') array from pivot_test;
 id |           array           
----+---------------------------
  1 | ["AZ","Sidmar","1"]
  2 | ["Kaihatsu","Suarez","9"]
  3 | ["Azuma","Neymar","11"]
  4 | ["OZ","Iniesta","8"]
(4 rows)

• Using UNNEST

dev=# select sub_query.id, split_array from (select id,SPLIT_TO_ARRAY(txt,',') array from pivot_test) sub_query, sub_query.array split_array;
 id | split_array 
----+-------------
  1 | "AZ"
  1 | "Sidmar"
  1 | "1"
  2 | "Kaihatsu"
  2 | "Suarez"
  2 | "9"
  3 | "Azuma"
  3 | "Neymar"
  3 | "11"
  4 | "OZ"
  4 | "Iniesta"
  4 | "8"
(12 rows)