Simple Array in MS SQL

[zgadson]

Can't seem to find an SQL equivalent to array_agg() that will work with Access. My OBDC data source is entirely locked down to read only, so CREATE or DECLARE statements aren't applicable either.



In short, I have a simple table for managing my warehouse with a column for the account name, and a second column for the aisle where product for the account is stored. Rather than having a single line returned for every aisle in use by a particular account, I'd prefer to have the account name listed once in one column, then an array of all the aisles used in the 2nd column.

SELECT
STNAME AS ACCOUNT,
MAAISL AS AISLE
FROM [SOURCE DATA]

IE rather than:

Account 1 | AisleA
Account 1 | AisleB
Account 1 | AisleC

I'd like:

Account 1 | AisleA, AisleB, AisleC

The simplicity is killing me but I just can't make anything work. Any help would be appreciated!

[Gicu]

See http://allenbrowne.com/func-concat.html or JoinFromArray @ http://forestbyte.com/vba-code-samples/

Cheers,

[kd2017]

Here you go: allenbrowne.com/func-concat.html

SELECT STNAME AS ACCOUNT, ConcatRelated("MAAISL", "[SOURCE DATA]", "STNAME=" & STNAME) AS AISLE FROM [SOURCE DATA]

[zgadson]

Here you go: allenbrowne.com/func-concat.html

SELECT STNAME AS ACCOUNT, ConcatRelated("MAAISL", "[SOURCE DATA]", "STNAME=" & STNAME) AS AISLE FROM [SOURCE DATA]

Thanks for the suggestion! I was initially getting the "ConcatRelated undefined" error (because I didn't have the function in my VBA) but now that I do I'm getting error messages for each account name it tries to concat. Some errors are "Syntax error (missing operator) in query expression" while others say "Too few parameters. Expected 1". Figured that could be a formatting issue but I've tried several things to no avail.

EDIT: I got it to ALMOST work using ConcatRelated("MAAISL", "[SOURCE DATA]", "STNAME= """ & STNAME & """") but Access gets stuck on Not Responding. It's got quite a lot of data to filter through but I let it go for at least 10 minutes hoping it would finish but gave up. Ideas?

[kd2017]

Ah very good, I see you discovered my coding error. That was a test, you passed!

Running vba functions in a query is generally slow. Especially this one. It would be best to limit the number of records you're running it on. First just try and get the function to work on 1 or 2 records to make sure you're using it correctly. How many records are we talking about?

Try grouping by the name first, then fetching the related strings. I should have done that in my example code.

Edit:
Your data does not look normalized. Also, do you have an index on the name field? Can you run a pass through select query instead?

[zgadson]

It would be best to limit the number of records you're running it on. First just try and get the function to work on 1 or 2 records to make sure you're using it correctly.

A much better idea than looping through my entire database. I've got maybe 50 accounts, but the entire SOURCE DATA is 53762 lines long because that's how many locations I have in my warehouse. I did get it to work when applying it to only one specific account name, but the array it returns has duplicates - any way to return only distinct values in the array?

Your data does not look normalized. Also, do you have an index on the name field? Can you run a pass through select query instead?

Not sure what you mean by index field. Assuming a field with unique values? I don't have a single field with unique values, but I do have an expression field that merges 3 fields into one to create a unique field. (Kept my sample code to a minimum for posting purposes.)

I'm also not familiar with pass through queries at all. Most of what I've accomplished in SQL world is a result of hours and hours of trial and error. But I think once I'm able to return a unique array from the concat function I can work my way to what I need.

Thanks again for the help! Definitely appreciate it!

[kd2017]

Here's another version of the query to try:

SELECT sbqry.ACCOUNT,
       ConcatRelated("MAAISL", "[SOURCE DATA]", "STNAME=""" & sbqry.ACCOUNT & """") AS AISLE
FROM   (SELECT DISTINCT STNAME AS ACCOUNT
        FROM   [SOURCE DATA]) AS sbqry;

The code before was running the ConcatRelated function on each and every row, including duplicates. I used a subquery to first remove duplicate rows and then ran the ConcatRelated function. It would probably still be a good idea to filter down the number of records further in the subquery.

[zgadson]

Here's another version of the query to try:

SELECT sbqry.ACCOUNT,
       ConcatRelated("MAAISL", "[SOURCE DATA]", "STNAME=""" & sbqry.ACCOUNT & """") AS AISLE
FROM   (SELECT DISTINCT STNAME AS ACCOUNT
        FROM   [SOURCE DATA]) AS sbqry;

Tried it, but Access throws a tantrum and freezes. Thinking I need something like this?

ConcatRelated("MAAISL", "[SOURCE DATA]", "STNAME= """ & STNAME <> STNAME & """")

[Minty]

In order to test this sort of function use the TOP 5 or TOP x Predicate function of the query.

It will restrict your data to a sensible number of records while you get the syntax correct and try and filter the subset to a more sensible number.

[kd2017]

Tried it, but Access throws a tantrum and freezes. Thinking I need something like this?

ConcatRelated("MAAISL", "[SOURCE DATA]", "STNAME= """ & STNAME <> STNAME & """")

I don't think so, not sure what you're trying to do there? STNAME <> STNAME would just evaluate out to False so you'd be searching for Where STNAME=False

The code I posted in #7 works for me in a local table with the limited data you posted in post #1. I'm thinking it's just too much for the access front end to process. You really need to limit the records -OR- run a pass through query to the sql server if you can.

[Minty]

Pass through query is definitely a good idea.

If it's a newer version of SQL server it has the String_Agg() function to do precisely what you want.

[kd2017]

Not sure what you mean by index field. Assuming a field with unique values? I don't have a single field with unique values, but I do have an expression field that merges 3 fields into one to create a unique field. (Kept my sample code to a minimum for posting purposes.)

An index doesn't have to be unique. Indexed field(s) are mapped out behind the scenes so the database engine can find records much quicker. I have to admit I'm not sure how it would work with a linked table. https://support.microsoft.com/en-us/...a-38c6c4f1a0ce

I'm also not familiar with pass through queries at all. Most of what I've accomplished in SQL world is a result of hours and hours of trial and error. But I think once I'm able to return a unique array from the concat function I can work my way to what I need.

Thanks again for the help! Definitely appreciate it!

A pass-through query allows you to just send the sql to the server and let the server munch on the data for you. That way you can use sql server's functions like string_agg. https://support.microsoft.com/en-us/...2-6dac62532a42

[zgadson]

Whoops. Meant ConcatRelated("MAAISL", "[SOURCE DATA]", "STNAME= """ & MAAISL <> MAAISL & """"). Something so that the array can't return a value that equals an existing value already included in the array. Might just have to create a small report for each account and link them together in the end to handle the data processing, but getting that distinct array is the key to moving forward.

No matter what I try I keep ending up with this:

[kd2017]

Whoops. Meant ConcatRelated("MAAISL", "[SOURCE DATA]", "STNAME= """ & MAAISL <> MAAISL & """").

That would be the same as I mentioned above. Try this instead

ConcatRelated("DISTINCT MAAISL", "[SOURCE DATA]", "STNAME=""" & STNAME & """")

Beware, we're creating more and more processing = even slower!

[zgadson]

Omg. I got EXACTLY what I was looking for. And it's lightning fast.

SELECT DISTINCT [SOURCE DATA].STNAME AS ACCOUNT, ConcatRelated("DISTINCT MAAISL","[SOURCE DATA]","STNAME=""" & STNAME & """") AS Expr1
FROM [SOURCE DATA]
WHERE ((([SOURCE DATA].[STNAME])<>''))
GROUP BY STNAME;

I think it's all thanks to the Group By because that way it's limiting it's looping to a single account entry and not 500 or more of each.

Thank you so much for all your help! Made my whole week to have this working and all thanks to you! Thank you!!!