Combining Rows-Opposite of Union

[LoneStar]

Hello,

I am tyring to combine several rows based on matching information..For example i have the following table.

IOSC: FEATURE:

00029 LH
00029 SWFTERM
00029 WATS
00031 1PTY
00031 BUS
00031 FR
00031 LS
00031 SWFBOTH
00031 TC
00573 FAXTHRU
00963 1PTY
00963 BUS
00963 FR
00963 LS
00963 SWFBOTH
00963 TC

What I want it to display is
IOSC: FEATURE:
00029 LH,SWFTERM,WATS
00031 PTY, BUS, FR, LS, SWFBOTH, TC
00573 FAXTHRU
00093 1PTY, BUS, FR, LS, SWFBOTH, TC

Can anyone think of a query that can be used to get the desired results?

Thank you.

[NeoPa]

The only way that I can think of is not strictly within SQL.
You'd need a function that saved what it had been passed by previous records (until change in IOSC) and returned the concatenated list so far.
The results of a SELECT query based on that would then have to be grouped and the Max() value of the returned string would be what you require.
Did I mention it was very kludgy?

[LoneStar]

I am not too savvy with VB..Any suggestions?

[NeoPa]

Paste this function into a module then run a query with the SQL below.

'Concat returns a list of the Features
'so far found for the current IOSC.
Public Function Concat(strIOSC As String, _
                       strFeature As String) As String
    Static strLastIOSC As String
    Static strFeatures As String
 
    If strIOSC = strLastIOSC Then
        strFeatures = strFeatures & ", " & strFeature
    Else
        strLastIOSC = strIOSC
        strFeatures = strFeature
    End If
    Concat = strFeatures
End Function

SELECT IOSC,
  Max(Concat(IOSC, Feature)) AS Features
FROM [YourTable]
GROUP BY IOSC

[LoneStar]

That worked perfectly! thank you so much!

[NeoPa]

An alternative answer which better displays the flexible nature of this code, is included below. Notice this doesn't affect the calling code (SQL) at all.

Paste this function into a module then run a query with the SQL below.

'Concat Returns lists of items which are within a grouped field
Public Function Concat(strGroup As String, _
                       strItem As String) As String
    Static strLastGroup As String
    Static strItems As String
 
    If strGroup = strLastGroup Then
        strItems = strItems & ", " & strItem
    Else
        strLastGroup = strGroup
        strItems = strItem
    End If
    Concat = strItems
End Function

SELECT IOSC,
  Max(Concat(IOSC, Feature)) AS Features
FROM [YourTable]
GROUP BY IOSC

[NeoPa]

Sometimes the GROUP BY clause is more complicated and contains more than one field. In this case the SQL format should be :

SELECT [GroupBy1],
       [GroupBy2],
       ...,
       [GroupByN],
       Max(Concat([GroupBy1] &
                  [GroupBy2] &
                  ... &
                  [GroupByN], Item)) AS Items
FROM [YourTable]
GROUP BY [GroupBy1],
         [GroupBy2],
         ...,
         [GroupByN]

[NeoPa]

Another thread, which discusses this in some detail, is Combining Multiple Rows of one Field into One Result.

If you don't feel that you understand after going through this thread then try that one too.

[Whizzo]

I've been having the same problem folks. The function and SQL call looks like a good route to me but why do I keep getting "Data type mismatch in criteria expression"? Both the fields I'm trying to process are text fields, I'm a bit stumped as to what the problem is!

[NeoPa]

I suggest you post your problem, clearly explained (with any relevant information) as a new thread. There's not enough information here to proceed on, and I doubt it would be helpful to discuss your problem in this thread.

[tuxalot]

Whizzo - I had the same issue and found that the code does not fair well with blank entries. Looking for a solution to this now.

Using the example above, if a FEATURE is blank in the table/query you will get this error.

HTH someone!

[NeoPa]

It's a valid point Tux, but simply dealt with.

In the calling code simply use the Nz() function around the field being passed as the [Item]. Thus the latest version of the SQL would be :

SELECT   [GroupBy1]
       , [GroupBy2]
       , ...
       , [GroupByN]
       , Max(Concat([GroupBy1] &
                    [GroupBy2] &
                    ... &
                    [GroupByN], Nz([Item], ''))) AS Items
FROM     [YourTable]
GROUP BY [GroupBy1]
       , [GroupBy2]
       , ...
       , [GroupByN]

[dguette]

@NeoPa
Great piece of code! the calling script was not quite working for me so I tweaked by substituting Max with Last. Works like a charm, thank you NeoPa!!

[NeoPa]

I'm really pleased to see/hear that people are still able to benefit from this work posted over four years ago.

You're very welcome.

[Barmaximus]

@NeoPa

Created a Bytes account to tell you this work is still helping people in 2019!

This Concat function (the one marked as solution) was exactly what I was looking for.

[NeoPa]

A hijack question has now been moved to a new thread called Procedure Name Same as Module Name. Please do not post new questions in existing threads.

[NeoPa]

BarMaximus:
@NeoPa

Created a Bytes account to tell you this work is still helping people in 2019!

Thank you kind sir.

I've moved your new question to a separate thread. I recognise you were very new when you created this so no probs with the hijack. I've also noticed that you seem to post well and with consideration.

Welcome to Bytes.com.

[EAJerman]

The solution works great!! However, I tried this solution with a table containing over 8,000 records and I received an error that read, "Reserved error (-1038); there is no message for this error"

I went back and used the original data from the example and it worked. So I used my data, but for only the first 50 records, and it also worked. Turns out it works through the first 1082 records. At record number 1083, it fails and gives the aforementioned error message. Any ideas?

[NeoPa]

Hi EAJerman.

Interesting points. I will respond in thread (unusually) as I believe this will be helpful directly to many who are interested in using such a solution.

First let me say that I wasn't aware of this behaviour. My tests were purely to assist someone with their problem so if I've ever used it in anger myself it's been very rarely and probably for noddy requirements such as the original request.

So here's what I think may be happening :
SQL engines, of whatever type, handle things the way their designers feel is best. Jet, ACE, T-SQL, etc are all different engines that convert the SQL standards, to a greater or lesser degree, into systems that we can use. There are variations and limitations in different interpretations of what's needed for a SQL engine.

Sometimes the work to process the request can be handled straightforwardly for smaller amounts of data but when the volumes get too large a different approach is necessary, for instance when available memory allows certain amounts of data to be handled but when more data is to be processed it wouldn't be able to handle it. So a different approach is used.

I suspect what's happening here is that your test has broken a threshold such that it ran out of resources to do it the more straightforward way, and the alternative approach somehow causes the calling of functions from the SQL to within your database/project not to work as expected.

This particular function relies on the data being processed by the SQL to progress through it in the order specified by the GROUP BY clause. If, due to being unable to handle the volumes the normal way, the SQL engine decides it's necessary to process the data unsorted first and only do the sorting and grouping after the rest has been completed, it would find itself in a pretty pickle (And unexpected as it believes the data to be simple unsorted data.) when there were multiple disparate values returned from the function.

Alternatively, maybe the Max() SQL function doesn't like dealing with more than a certain number of entries. Or even has a limit to how much string data it can handle in total.

As you can see, I don't KNOW the answer. I can but guess based on what I do understand about how things must be working behind the scenes. Even that is limited and unconfirmed so I offer this purely on a Caveat Emptor basis. No guarantees - just my best thinking.

Nevertheless, I hope it helps you and others to a better understanding of working in databases.

[cactusdata]

You may obtain better results using my DJoin function documented here:

Link Removed

Full code is also on GitHub: VBA.DJoin

Study the demo application. It holds a query, Join, that aggregates about 160K records in a few seconds.

[Acafass]

@NeoPa - Just checking in here in 2022 -- just tried out your code and it hit it purrrrfectly. Thanks!

[NeoPa]

Hi Acafass.

I'm very pleased to hear it :-)

I suspect the ever increasing View counts indicate how some of the more fundamental and common requests are continually being answered by old threads and, especially, the article threads (This one is not but it's a rare exception which continues to clock up the Views).