CODEWITHSUNDEEP
sqlarraysstringgoogle-bigquery
Norah Jones
Norah Jones

Reputation: 467

STRING_AGG() in one column and distinct values in column next to

I am currently using such query to aggregate strings:

select
  STRING_AGG(pet_name) over (partition by id, name, second_id, second_name ORDER BY pet_id) pet_names
  id, 
  name, 
  second_id, 
  second_name
 FROM `example`

|---------------------|--------|------------------|------------------|------------------|
|      pet_names      |   id   |        name      |     second_id    |   second_name    |
|---------------------|--------|------------------|------------------|------------------|
|   [cat, dog, bird]  |    1   |       anna       |          2       |       rose       |    
|---------------------|--------|------------------|------------------|------------------|
|  [cat, bear, tiger] |    2   |       kate       |          3       |       mike       |  
|---------------------|--------|------------------|------------------|------------------|
|    [cat, tiger]     |    3   |       john       |          2       |       bate       | 
|---------------------|--------|------------------|------------------|------------------|

However, I would like to end up with such table (here an example for the first row of the above shown table):

|---------------------|--------|------------------|------------------|------------------|--------|
|      pet_names      |   id   |        name      |     second_id    |   second_name    |pet_name|
|---------------------|--------|------------------|------------------|------------------|--------|
|   [cat, dog, bird]  |    1   |       anna       |          2       |       rose       |   cat  |
|---------------------|--------|------------------|------------------|------------------|--------|
|   [cat, dog, bird]  |    1   |       anna       |          2       |       rose       |   dog  | 
|---------------------|--------|------------------|------------------|------------------|--------|
|   [cat, dog, bird]  |    1   |       anna       |          2       |       rose       |   bird |
|---------------------|--------|------------------|------------------|------------------|--------|

When I try:

select
  STRING_AGG(pet_name) over (partition by id, name, second_id, second_name ORDER BY pet_id) pet_names
  id, 
  name, 
  second_id, 
  second_name,
  pet_name
 FROM `example`

It doesn't really work properly returning [cat] for pet_name "cat", then [cat, dog] for pet_name "dog" etc.

Upvotes: 1

Views: 2195

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

Remove the order by:

select string_agg(pet_name) over (partition by id, name, second_id, second_name) as pet_names,
       . . . 
from `example`

The order by makes the window function "cumulative" within each group defined by the partition by keys. Without the order by, one value is returned for all rows with the same partition by keys.

Upvotes: 3

Related Questions