I know that in SQL I can use the 'String_agg(Test1, ',')' function for grouping rows and concatenate values in a selected field ('Test1' in this case).
For Example:
I have a query that the result without using String_agg on 'Buyer' field is:
**Key** | **Buyer** | **MP**
1 | Josh | Gregory
1 | Bred | Gregory
2 | John | Ethan
The expected results when using String_agg is:
**Key** | **Buyer** | **MP**
1 | Josh, Bred | Gregory
2 | John | Ethan
But the problem is that I'm trying to execute it in SQL query which retrieves data to Excel file from another Excel file and it fails because of an error that seems like the Excel query doesn't know the String_agg function.
The query is:
SELECT `Sheet1$`.Key, string_agg(`Sheet1$`.Buyer, `, `) AS `Buyer`, `Sheet1$`.MP
FROM `C:\Input\Data.xls`.`Sheet1$` `Sheet1$`
GROUP BY 2
ORDER BY `Sheet1$`.Key
Screenshot:
Error:
Someone can help me and tell me how should I correct my query to make it works?
Thank you!
Problem: Excel is not a database.
You are trying to used advanced query functionality in a spreadsheeting package, which is sometimes somewhat supported in some versions of excel, uses lots of processor power, causes serious issues as soon as a user moves anything on the sheet, or the file itself, and is not really what it was designed to do.
Solution: Use a database.
