By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,917 Members | 1,305 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Producing a List from Multiple Records

NeoPa
Expert Mod 15k+
P: 31,492
A number of posters have asked to be shown how to produce a list of items from multiple records which are (potentially) grouped together.
Take the following data for instance (from a table called [tblForum]) :
Expand|Select|Wrap|Line Numbers
  1. Zone             Forum
  2. Community        Introductions
  3. Community        Community Cafe
  4. Community        Software Development
  5. Community        Jobs / Contract Work
  6. Community        Experts Panel
  7. Programming      C++ / C
  8. Programming      Java
  9. Programming      .NET
  10. Programming      Visual Basic
  11. Programming      Python
  12. Web Development  PHP
  13. Web Development  Ruby / Rails
  14. Web Development  Perl
  15. Web Development  ASP
  16. Web Development  ColdFusion
  17. Web Development  Javascript / Ajax
  18. Web Development  Flash
  19. Web Development  XML
  20. Web Development  WAP / WML
  21. Web Development  HTML / CSS
  22. Database Help    MySQL
  23. Database Help    Oracle
  24. Database Help    SQL Server
  25. Database Help    PostgreSQL
  26. Database Help    Access
  27. Database Help    DB2
  28. Sys Admin & OS   Apache
  29. Sys Admin & OS   IIS
  30. Sys Admin & OS   Networking
  31. Sys Admin & OS   Linux / Unix / BSD
  32. Sys Admin & OS   Windows
If you wanted this to be grouped together into lists (as below) then read on.
Expand|Select|Wrap|Line Numbers
  1. Zone             Forums
  2. Community        Introductions, Community Cafe, Software Development, Jobs / Contract Work, Experts Panel
  3. Programming      C++ / C, Java, .NET, Visual Basic, Python
  4. Web Development  PHP, Ruby / Rails, Perl, ASP, ColdFusion, Javascript / Ajax, Flash, XML, WAP / WML, HTML / CSS
  5. Database Help    MySQL, Oracle, SQL Server, PostgreSQL, Access, DB2
  6. Sys Admin & OS   Apache, IIS, Networking, Linux / Unix / BSD, Windows
Firstly, create a function in a standard module similar to the example code attached :
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
The SQL for calling this function would be of the form :
Expand|Select|Wrap|Line Numbers
  1. SELECT   [Zone],
  2.          Max(Concat([Zone], [Forum])) AS [Forums]
  3. FROM     [tblForum]
  4. GROUP BY [Zone]
Another form of this (to drop duplicates) would be :
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.         If InStr(", " & strItems & ", ", ", " & strItem & ", ") = 0 Then _
  9.             strItems = strItems & ", " & strItem
  10.     Else
  11.         strLastGroup = strGroup
  12.         strItems = strItem
  13.     End If
  14.     Concat = strItems
  15. End Function
Apr 30 '07 #1
Share this Article
Share on Google+
1 Comment


100+
P: 332
This is nice.

I combined the two functions by adding a third parameter "uniq"
Expand|Select|Wrap|Line Numbers
  1. 'Concat Returns lists of items which are within a grouped field
  2. 'Set uniq=-1 (True) to return only unique values.
  3. Public Function ConcatLine(strGroup As String, _
  4.                        strItem As String, uniq As Boolean) As String
  5.     Static strLastGroup As String
  6.     Static strItems As String
  7.     Static i As Integer
  8.  
  9.     If strGroup = strLastGroup Then
  10.         If uniq Then
  11.             If InStr(", " & strItems & ", ", ", " & strItem & ", ") = 0 Then
  12.                 strItems = strItems & ", " & strItem
  13.             End If
  14.         Else
  15.             strItems = strItems & ", " & strItem
  16.         End If
  17.     Else
  18.         strLastGroup = strGroup
  19.         strItems = strItem
  20.     End If
  21.     ConcatLine = strItems
  22.     i = i + 1
  23.     Debug.Print i & " "; Len(ConcatLine) & " " & ConcatLine
  24. End Function
Jan 12 '12 #2