Is there a way in Vertica to unpivot data?

Dave_1

I am interested in finding a way to transform data from a denormalized table with a structure like the following:

item id | metric a | metric b
    1         100           50
    2           30           75

To a new key-value format like the following:

item id | metric name | metric value
    1            metric a       100
    1            metric b        50
    2            metric a        30
    2            metric b        75

I can see how to transform from key-value to denormalized using a CASE/DECODE style approach; however, I don't see how to "unpivot" data like in the example above.  Is there a Vertica built-in function to do this?  If not, has anyone been able to do this using a "pure SQL" approach?

Thanks in advance,

Dave

[Deleted User]

Maybe union the different metrics like this :

select item_id, 'metric_a' as metric, metric_a as value from test
union all 
select item_id, 'metric_b' as metric, metric_b as value from test ;

 item_id |  metric  | value
---------+----------+-------
       1 | metric_a |   100
       1 | metric_b |    50
       2 | metric_a |    30
       2 | metric_b |    75


HTH
Eugenia

Dave_1

Thanks, Eugenia.  In my case, I have hundreds of tables with column counts that can exceed over 100 columns, so, I'm really looking for a way to perform this "unpivot" without having to explicitly name every metric column like you have in your example.  

knicely87

dbadmin=> select * from public.denorm order by 1;
 item_id | metric_a | metric_b
---------+----------+----------
       1 |      100 |       50
       2 |       30 |       75
(2 rows)

dbadmin=> select * from public.not_denorm order by 1;
 item_id | metrice_name | metric_value
---------+--------------+--------------
(0 rows)

dbadmin=> \! /opt/vertica/bin/vsql -qAtc "select 'insert into public.not_denorm select ' || TO_CHAR(b.item_id) || ' item_id, ''' || c.column_name || ''' metric_name, (select a.' || c.column_name || ' from denorm a where a.item_id = ' || b.item_id || ') ' || ' metric_value; commit;' from denorm b join columns c on c.table_schema = 'public' and c.table_name = 'denorm' and c.column_name <> 'item_id' order by item_id;" | /opt/vertica/bin/vsql -qAt
1
1
1
1
dbadmin=> select * from public.not_denorm order by 1;
 item_id | metrice_name | metric_value
---------+--------------+--------------
       1 | metric_a     |          100
       1 | metric_b     |           50
       2 | metric_a     |           30
       2 | metric_b     |           75
(4 rows)