1

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:

Query screenshot

Error:

Error Screenshot

Someone can help me and tell me how should I correct my query to make it works?

Thank you!

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Gilad K
  • 17
  • 5

2 Answers2

0

Have a bit of a look at the excel 'concatenate' function.

I believe you can use it as CONCAT() also.

Also see this SO question: Concatenation in SQL select query on Excel sheet

Hope this helps.

Community
  • 1
  • 1
Tim Ogilvy
  • 1,923
  • 1
  • 24
  • 36
  • The CONCAT() function depends on the number of fields you want to concatenate. The String_agg not. I don't know how many 'Buyer' will be after executing the query. I want to concatenate them, and just them, without other fields. Thanks. – Gilad K Mar 27 '16 at 12:31
  • @GiladK you are using Excel as a database, which it is not. Consider using an actual database? I am planning on printing myself a T-Shirt that says 'Excel is not a database'. – Tim Ogilvy Mar 27 '16 at 12:38
  • Lol :) you're right, but that's what I have now. I tried now to move all 'Excel DB' to an Access table and make a connection to that Access file and again - the 'String_agg' function doesn't work. I don't know what to do. but thanks for your time! – Gilad K Mar 27 '16 at 13:47
0

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.

Tim Ogilvy
  • 1,923
  • 1
  • 24
  • 36