CODEWITHSUNDEEP
sqlgoogle-bigquery
Fairuz Yassar
Fairuz Yassar

Reputation: 139

How to concate multiple row into single row in ordered distinct value?

I want to concate multiple row into single row using bigquery

I have table like this

A           |    B    |   C     |   D      |
09-10-2019  |    1    |   math  |  social  |
09-10-2019  |    1    |   math  |  science |
09-10-2019  |    1    |   math  |  science |

i want result table like this

A           |    B    |   C     |   D               |
09-10-2019  |    1    |   math  |  science, social  |

but my code got result like this

A           |    B    |   C               |        D                   |
09-10-2019  |    1    |   math,math,math  |  science, science, social  |

i am order using 

group_concat(unique(C),",") over(partition by A, B ORDER BY C)
group_concat(unique(D),",") over(partition by A, B ORDER BY D)

but i got an error

Upvotes: 2

Views: 60

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173210

Below is for BigQuery Standard SQL 

#standardSQL
SELECT a, b, 
  (SELECT STRING_AGG(c ORDER BY c) FROM UNNEST(c_distinct) c) c,
  (SELECT STRING_AGG(d ORDER BY d) FROM UNNEST(d_distinct) d) d
FROM (
  SELECT a, b,
    ARRAY_AGG(DISTINCT c) AS c_distinct,
    ARRAY_AGG(DISTINCT d) AS d_distinct
  FROM `project.dataset.table`
  GROUP BY a, b
)

you can test, play with above using sample data from your question as in below example 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT '09-10-2019' a, 1 b, 'math' c, 'social' d UNION ALL
  SELECT '09-10-2019', 1, 'math', 'science' UNION ALL
  SELECT '09-10-2019', 1, 'math', 'science' 
)
SELECT a, b, 
  (SELECT STRING_AGG(c ORDER BY c) FROM UNNEST(c_distinct) c) c,
  (SELECT STRING_AGG(d ORDER BY d) FROM UNNEST(d_distinct) d) d
FROM (
  SELECT a, b,
    ARRAY_AGG(DISTINCT c) AS c_distinct,
    ARRAY_AGG(DISTINCT d) AS d_distinct
  FROM `project.dataset.table`
  GROUP BY a, b
)

with result 

Row a           b   c       d    
1   09-10-2019  1   math    science,social

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271241

Use string_agg():

select a, b,
       string_agg(c, ',' order by c) as cs,
       string_agg(d, ',' order by d) as ds
from t
group by a, b;

That said, I would recommend arrays instead:

select a, b,
       array_agg(c order by c) as cs,
       array_agg(d order by d) as ds
from t
group by a, b;

Upvotes: 0

Related Questions