How to pivot and create a new table from multiple data rows
Build intelligent, mission-critical applications using a scalable, hybrid database platform that has everything built in—from in-memory performance and advanced security to in-database analytics.
How do you pivot and create a new table from multiple data rows if you have a table that contains multiple versions of a data row? You can simply use the CASE and MAX in your SELECT statement. To illustrate this, let's create a sample table that will hold multiple version of user data and populate the table with sample data as such:
q create 2 table table_name 3 ( 4 id int, 5 field_num int, 6 desc_text varchar(100), 7 primary key (id) 8 ); 9 10 INSERT INTO table_name (id, field_num, desc_text) 11 VALUES (1, 2, 'adam'), 12 (2, 3, 'adam'), 13 (3, 2, 'blake'), 14 (4, 1, 'blake'), 15 (5, 5, 'blake'), 16 (6, 3, 'oliver'), 17 (7, 1, 'oliver'), 18 (8, 4, 'oliver'), 19 (9, 7, 'oliver');
We will then construct a SELECT statement using CASE and MAX to give us a new table that will give us a new table that will represent the maximum number of each "desc_text" field.
1 SELECT adam_max_num = MAX(CASE A.desc_text WHEN 'adam' THEN A.field_num ELSE 0 END), 2 blake_max_num = MAX(CASE A.desc_text WHEN 'blake' THEN A.field_num ELSE 0 END), 3 oliver_max_num = MAX(CASE A.desc_text WHEN 'oliver' THEN A.field_num ELSE 0 END) 4 FROM table_name A 5 ;