We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Is there a way in Vertica to unpivot data? — Vertica Forum

Is there a way in Vertica to unpivot data?

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

Comments

  • 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
  • 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.  
  • 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)


Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file