BigQuery - Concatenate multiple rows and columns into a single row

Mr.Mahajan

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
Mikhail Berlyant

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.

edited at
0

Comments

0 comments
Login to comment

Related

Single row to multiple columns and rows

Joining two tables, multiple rows into a single row different columns

BigQuery - Concatenate multiple rows into a single row

Combine Multiple Rows And Columns Into A Single Row In SQL

PHP - SQL: Combine two rows into single row with multiple columns

Transform rows with common key to single row with multiple columns

Spark: How to convert multiple rows into single row with multiple columns?

How to convert n rows into a single row with multiple columns

Use LINQ to concatenate multiple rows list with same value into single row

Transpose single row with multiple columns into multiple rows of two columns

Pandas: Adding data from multiple rows into extra columns for a single row

Summing multiple columns across a single row, for multiple rows

BigQuery - Concatenate multiple columns into a single column for large numbers of columns

How to get columns from multiple rows in a single row in SQL

SQL Server - Display columns from multiple rows as single row

How do i split a single row of columns into multiple rows and columns?

Oracle SQL convert a single row with multiple columns into multiple rows

MSSQL 2012 - How to combine multiple rows and columns into single row

Concatenate multiple rows to single row with condition check using recursive CTE

Convert a single row into multiple rows by the columns in Postgresql

Converting multiple rows into single row with multiple columns

Convert single row into multiple rows Bigquery SQL

Is there a way to aggregate multiple Pandas rows into a single row with extra columns?

Concatenate/join multiple rows of strings into one single row for the entire dataframe

Concatenate multiple rows of specific columns into one row pandas

Pivot multiple rows (2 columns) into a single row

How to concatenate multiple rows list with same value into single row

Split Single Row into Multiple Rows Based on Two Timestamp Columns

Excel - condense multiple rows into a single row of multiple columns