Combining Rows-Opposite of Union

[LoneStar]

Hello,

I am tyring to combine several rows based on matching information..Fo r 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]

Combining Rows-Opposite of Union

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

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.