424,066 Members | 2,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,066 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+
29 Replies


NeoPa
Expert Mod 15k+
P: 31,170
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,170
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,170
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,170
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,170
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,170
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,170
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,170
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: 18
@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. Function worked perfectly yesterday. However, today I'm getting an error "Undefined Function 'Concat' In Expression" with no change in my code. I created a new module and changed Concat to Concat2 and it worked. I'm new to VBA and would love to know how to avoid this in the future.

Also, does this work when using a query or subquery in the FROM clause? For me, this yields no concatenation, but creating a table from the same query and referencing that works. Any advice would be great.
2 Weeks Ago #15

twinnyfo
Expert Mod 2.5K+
P: 2,780
Barmaximus,

Welcome to Bytes!

However, today I'm getting an error "Undefined Function 'Concat' In Expression" with no change in my code.
Any chance that you named the Module "Concat"? This would cause some ambiguity within the DB.
2 Weeks Ago #16

P: 18
@twinnyfo of course I did. Changing the name of the module fixed this. Any thoughts on why it worked fine previously?
2 Weeks Ago #17

twinnyfo
Expert Mod 2.5K+
P: 2,780
You probably tried using it prior to saving the Module's Name.

When you call Concat and you have a Module named Concat and a Procedure with the same name, the DB doesn't know how to properly disambiguate. When you build the procedure, your Module was named "Module1" by default--so there was no issue.

These types of issues have had me scratching my head for hours in the past. Now I know to use better naming conventions. So, I would have saved the Module as "modConcat", which contained a Procedure named "Concat". Some go further and name Functions "fnConcat" to further prevent any disambiguation.

Hope this hepps!

Let us know if there are any other headscratchers you need hepp with!
2 Weeks Ago #18

P: 18
Thanks @twinnyfo . Any thoughts on this function not yielding a concatenation when called in a Select query with a sub Select query in the From clause? I'm trying to use the function on data generated from a query. However, it doesn't yield the desired results unless I create a table that is identical to the query output.
2 Weeks Ago #19

twinnyfo
Expert Mod 2.5K+
P: 2,780
My only thought is that the SQL engine gets confused or overwhelmed when going into the subqueries. For example, you can refer to open forms for criteria in your queries, but you can't refer to open forms when the criteria is in a subquery (if I remember my experiences correctly).

What have you been trying? If you post your SQL, we can take a look and see if we can decipher anything.
2 Weeks Ago #20

P: 18
Below is my code. The subquery finds all keyword matches from a reference library [Component Library] to [Data - Inventory].Description and returns the keyword found and the associated Type. This yields more than one row per component where multiple matches are found. Works perfectly as a standalone query. When inserted as a subquery, it yields one match per component, no concatenation. However, when I create a table identical to the results of the query, I can achieve the desired concatenation.

Expand|Select|Wrap|Line Numbers
  1. SELECT x.Component, 
  2.        Max(x.Description), 
  3.        Max(Concat(Nz(x.Component), Nz(x.PartType)))
  4. FROM (SELECT [Data - Inventory].Component, 
  5.              [Data - Inventory].Description, 
  6.              [Component Library].Keyword AS [Key], 
  7.              [Component Library].Type AS PartType
  8.       FROM [Component Library] 
  9.       LEFT JOIN [Data - Inventory] 
  10.       ON [Data - Inventory].Description LIKE "*" &  [Component Library].Keyword & "*") 
  11.       AS x
  12. GROUP BY x.Component;
2 Weeks Ago #21

twinnyfo
Expert Mod 2.5K+
P: 2,780
I wonder if SQL just doesn't like using that function with JOINs?
2 Weeks Ago #22

P: 18
It's possible. I'm extremely new to SQL. But based on my limited knowledge, I would assume it would complete the entire subquery and return it to the main query in the form of a table regardless of the method used to achieve the data.
2 Weeks Ago #23

twinnyfo
Expert Mod 2.5K+
P: 2,780
I'm sure NeoPa will have an intelligent answer on this one.

When SQL executes subqueries, I don't know if the query runs underneath to its full extent and then JOINs or if the top query executes, and the executes a subquery based upon the JOIN.

I know when I have chosen to use sub-queries (instead of things which are frowned upon, like using a Domain Aggregate Function) that I loose the flexibility of using that query as an updateable query. I've recently tried to eliminate all my DA functions from my queries (which is the "right thing to do") but had to go back because I was using them to update tables.

SQL can be finicky sometimes.
2 Weeks Ago #24

P: 18
The end goal of this query is to perform a one time Update/population of a Type field in my [Data - Inventory] table. I have been building it in steps to ease in troubleshooting. I have only been using SQL for a few weeks and am trying to avoid starting bad habits, so any advice is greatly appreciated!
2 Weeks Ago #25

twinnyfo
Expert Mod 2.5K+
P: 2,780
I can tell you right now that using this method will not allow you to use it as an UPDATE query.

Ther eis a part of me that wonders about your subquery design. Will something like this work?

Expand|Select|Wrap|Line Numbers
  1. SELECT [Data - Inventory].Component, 
  2.        [Data - Inventory].Description, 
  3.        Max(Concat(Nz([Data - Inventory].Component), Nz([Component Library].Type))) AS PartTypes
  4. FROM [Component Library] 
  5. LEFT JOIN [Data - Inventory] 
  6. ON [Data - Inventory].Description LIKE "*" &  [Component Library].Keyword & "*" 
  7. GROUP BY [Data - Inventory].Component;
2 Weeks Ago #26

P: 18
This works but yields the same result as the non-concatenated result I described previously. I like the idea of this suggestion avoiding the need of a subquery if I can get it to work.
2 Weeks Ago #27

twinnyfo
Expert Mod 2.5K+
P: 2,780
And, as I have been playing with this little function, I can't get the concatenation to work with any type of join. It works fine if there is only one table.

You might have to use VBA to cycle through all your required components and concatenate on the fly and update your table as you go.
2 Weeks Ago #28

twinnyfo
Expert Mod 2.5K+
P: 2,780
We may be a bit off center from this thread. I recommend you begin a new thread and we can work thorugh the specifics if you need.
2 Weeks Ago #29

P: 18
Done.
2 Weeks Ago #30

Post your reply

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