By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,831 Members | 1,031 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,831 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+
16 Replies


NeoPa
Expert Mod 15k+
P: 31,302
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,302
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,302
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,302
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,302
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,302
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,302
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,302
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,302
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,302
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

Post your reply

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