How to pivot and create a new table from multiple data rows

walden systems, geeks corner, programming, languages, developer, sql, datetime, varchar, time, date, rows, table, pivot
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 ;