By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,370 Members | 2,780 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,370 IT Pros & Developers. It's quick & easy.

Combining Rows-Opposite of Union

P: 8
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.
Nov 27 '06 #1

✓ answered by 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.
Expand|Select|Wrap|Line Numbers
  1. 'Concat Returns lists of items which are within a grouped field
  2. Public Function Concat(strGroup As String, _
  3.                        strItem As String) As String
  4.     Static strLastGroup As String
  5.     Static strItems As String
  6.  
  7.     If strGroup = strLastGroup Then
  8.         strItems = strItems & ", " & strItem
  9.     Else
  10.         strLastGroup = strGroup
  11.         strItems = strItem
  12.     End If
  13.     Concat = strItems
  14. End Function
Expand|Select|Wrap|Line Numbers
  1. SELECT IOSC,
  2.   Max(Concat(IOSC, Feature)) AS Features
  3. FROM [YourTable]
  4. GROUP BY IOSC

Share this Question
Share on Google+
19 Replies


NeoPa
Expert Mod 15k+
P: 31,494
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?
Nov 27 '06 #2

P: 8
I am not too savvy with VB..Any suggestions?
Nov 28 '06 #3

NeoPa
Expert Mod 15k+
P: 31,494
Paste this function into a module then run a query with the SQL below.
Expand|Select|Wrap|Line Numbers
  1. 'Concat returns a list of the Features
  2. 'so far found for the current IOSC.
  3. Public Function Concat(strIOSC As String, _
  4.                        strFeature As String) As String
  5.     Static strLastIOSC As String
  6.     Static strFeatures As String
  7.  
  8.     If strIOSC = strLastIOSC Then
  9.         strFeatures = strFeatures & ", " & strFeature
  10.     Else
  11.         strLastIOSC = strIOSC
  12.         strFeatures = strFeature
  13.     End If
  14.     Concat = strFeatures
  15. End Function
Expand|Select|Wrap|Line Numbers
  1. SELECT IOSC,
  2.   Max(Concat(IOSC, Feature)) AS Features
  3. FROM [YourTable]
  4. GROUP BY IOSC
Nov 28 '06 #4

P: 8
That worked perfectly! thank you so much!
Nov 28 '06 #5

NeoPa
Expert Mod 15k+
P: 31,494
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.
Expand|Select|Wrap|Line Numbers
  1. 'Concat Returns lists of items which are within a grouped field
  2. Public Function Concat(strGroup As String, _
  3.                        strItem As String) As String
  4.     Static strLastGroup As String
  5.     Static strItems As String
  6.  
  7.     If strGroup = strLastGroup Then
  8.         strItems = strItems & ", " & strItem
  9.     Else
  10.         strLastGroup = strGroup
  11.         strItems = strItem
  12.     End If
  13.     Concat = strItems
  14. End Function
Expand|Select|Wrap|Line Numbers
  1. SELECT IOSC,
  2.   Max(Concat(IOSC, Feature)) AS Features
  3. FROM [YourTable]
  4. GROUP BY IOSC
Jan 21 '07 #6

NeoPa
Expert Mod 15k+
P: 31,494
Sometimes the GROUP BY clause is more complicated and contains more than one field. In this case the SQL format should be :
Expand|Select|Wrap|Line Numbers
  1. SELECT [GroupBy1],
  2.        [GroupBy2],
  3.        ...,
  4.        [GroupByN],
  5.        Max(Concat([GroupBy1] &
  6.                   [GroupBy2] &
  7.                   ... &
  8.                   [GroupByN], Item)) AS Items
  9. FROM [YourTable]
  10. GROUP BY [GroupBy1],
  11.          [GroupBy2],
  12.          ...,
  13.          [GroupByN]
Jan 21 '07 #7

NeoPa
Expert Mod 15k+
P: 31,494
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.
Aug 25 '08 #8

P: 44
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!
Mar 30 '09 #9

NeoPa
Expert Mod 15k+
P: 31,494
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.
Mar 31 '09 #10

tuxalot
100+
P: 200
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!
Aug 23 '11 #11

NeoPa
Expert Mod 15k+
P: 31,494
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT   [GroupBy1]
  2.        , [GroupBy2]
  3.        , ...
  4.        , [GroupByN]
  5.        , Max(Concat([GroupBy1] &
  6.                     [GroupBy2] &
  7.                     ... &
  8.                     [GroupByN], Nz([Item], ''))) AS Items
  9. FROM     [YourTable]
  10. GROUP BY [GroupBy1]
  11.        , [GroupBy2]
  12.        , ...
  13.        , [GroupByN]
Aug 24 '11 #12

P: 1
@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!!
Nov 23 '15 #13

NeoPa
Expert Mod 15k+
P: 31,494
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.
Nov 24 '15 #14

P: 20
@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.
Jan 4 '19 #15

NeoPa
Expert Mod 15k+
P: 31,494
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.
Apr 13 '19 #16

NeoPa
Expert Mod 15k+
P: 31,494
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.
Apr 13 '19 #17

P: 1
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?
Aug 30 '19 #18

NeoPa
Expert Mod 15k+
P: 31,494
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.
Sep 4 '19 #19

P: 18
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.
3 Weeks Ago #20

Post your reply

Sign in to post your reply or Sign up for a free account.