0

I have access to a snowflake data storage system that is supplied by a third party, the system has a table that I need to get some data out of. The table has three columns that I'm interested in the first one is called insert id the second is called labels, the third column is called value, which has the response that was entered by the end-user.

I need to do a select query that that selects the value from column value into its own column based on the value that is in the labels column. such as SELECT value as column1 from table where labels = textbox1_lbl1 and SELECT value as column2 from table where labels = textbox2_lbl1

I know I probably haven't explained this in the correct manner to hopefully these mock tables help.

This is what I have got

┌───────────┬───────────────┬────────┐
│ insert id │    labels     │ value  │
├───────────┼───────────────┼────────┤
│         1 │ textbox1_lbl1 │ blue   │
│         1 │ textbox2_lbl1 │ red    │
│         1 │ textbox3_lbl1 │ green  │
│         1 │ textbox4_lbl1 │ yellow │
│         2 │ textbox1_lbl1 │ blue   │
│         2 │ textbox2_lbl1 │ red    │
│         2 │ textbox3_lbl1 │ green  │
│         2 │ textbox4_lbl1 │ yellow │
└───────────┴───────────────┴────────┘

This is what I want to get

┌───────────┬─────────┬─────────┐
│ insert_id │ column1 │ column2 │
├───────────┼─────────┼─────────┤
│         1 │ blue    │ red     │
│         2 │ blue    │ red     │
└───────────┴─────────┴─────────┘

I have been trying to work out what query I should be using but I'm drawing a blank any help with the query would be appreciated it a properly simple query and I'm being stupid.

BLUENUN34
  • 3
  • 2

1 Answers1

0

I think you want conditional aggregation:

select insert_id,
       max(case when labels = 'textbox1_lbl1' then value end) as column_1,
       max(case when labels = 'textbox1_lbl2' then value end) as column_2
from t
group by insert_id;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786