Help in restructuring table

Hi there,

having a quite hard time to get used to flux coming from SQL :crazy_face:
Could you please help me with a table restructuring task?

This is example data for my input table format:

id val0 val1 val2
100 1 5 7
101 8 5 4
102 9 9 1

And this is what I want to get:

id valno val
100 0 1
100 1 5
100 2 7
101 0 8
101 1 5
101 2 4
102 0 9
102 1 9
102 2 1

Little bit like a “reverse pivot” is what I need…
Any help is appreciated!

Best,
Keith

@sauvant There is an issue about this exact functionality. Please feel free to comment on it: Add unpivot functionality · Issue #2539 · influxdata/flux · GitHub

It is possible to do what you want, but it isn’t very straight forward. The example below uses array.from to build a stream of tables that matches your data, so you don’t actually need it when you do this operation on your actual data, but it creates 3 separate streams of tables (one for each value number), restructures them to what you need, unions them back together and applies the necessary sorting:

import "array"

data = array.from(rows: [
    {id: 100, val0: 1, val1: 5, val2: 7},
    {id: 101, val0: 8, val1: 5, val2: 4},
    {id: 102, val0: 9, val1: 9, val2: 1},
])

d0 = data |> keep(columns: ["id", "val0"]) |> map(fn: (r) => ({id: r.id, valno: 0, val: r.val0}))
d1 = data |> keep(columns: ["id", "val1"]) |> map(fn: (r) => ({id: r.id, valno: 1, val: r.val1}))
d2 = data |> keep(columns: ["id", "val2"]) |> map(fn: (r) => ({id: r.id, valno: 2, val: r.val2}))

union(tables: [d0, d1, d2])
  |> sort(columns: ["id", "valno"])