CODEWITHSUNDEEP
sqlstructgoogle-bigqueryarray-agg
s.m
s.m

Reputation: 1

BigQuery SQL Select that returns Key Value Pairs and not two seperate columns

I am trying to merge 2 of my columns in my select statement on bigquery so that I have one column with key value pairs rather than having 2 columns (one with the key and one with the value). I have attempted to use array_agg() but whenever I do it causes an error in the first line of my statement saying: "An expression references column colName which is neither grouped nor aggregated"

Any suggestions as to how to do this or what may be causing my array_agg error would be greatly appreciated.

Upvotes: 0

Views: 4806

Answers (1)

Graham Polley
Graham Polley

Reputation: 14791

There's limited information in your question, but I'll have a stab at it anyway. Here's some examples using arrays, string concatenation, and structs:

#standardSQL
WITH
  key_values AS (
  SELECT
    'key1' AS k,
    'value1' AS v
  UNION ALL
  SELECT
    'key2' AS k,
    'value2' AS v)
SELECT
  [k,
  v] AS kv_array,
  CONCAT(k,':',v) AS kv_concat,
  STRUCT(k,
    v) AS kv_struct
FROM
  key_values

enter image description here

Upvotes: 4

Related Questions