I have column like this
id|name | Country
1|John | usa
1|Tom | Canada
1|Bob | Italy
2|Jack | China
2|Tim | USA
I need output like
id|Text
1| John USA , Tom Canada , Bob Italy
2|Jack China, Tim USA
Below is for BigQuery Standard SQL
#standardSQL
SELECT id, STRING_AGG(CONCAT(name, ' ' , country)) text
FROM `project.dataset.table`
GROUP BY id
You can test, play with above using sample data from your question as in example below
#standardSQL
WITH `project.dataset.table` AS (
SELECT 1 id, 'John' name, 'Usa' country UNION ALL
SELECT 1, 'Tom', 'Canada' UNION ALL
SELECT 1, 'Bob', 'Italy' UNION ALL
SELECT 2, 'Jack', 'China' UNION ALL
SELECT 2, 'Tim', 'USA'
)
SELECT id, STRING_AGG(CONCAT(name, ' ' , country)) text
FROM `project.dataset.table`
GROUP BY id
with output
Row id text
1 1 John Usa,Tom Canada,Bob Italy
2 2 Jack China,Tim USA
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments