Virtual Comma Delimited List

Results 1 to 2 of 2

Thread: Virtual Comma Delimited List

  1. #1
    Join Date
    Dec 1969

    Default Virtual Comma Delimited List

    I&#039;ve got the following database structure:<BR>Products - (ProductCode, ProductName, etc.)<BR>Options - (OptionID, OptionName, etc.)<BR>Options_ApplyTo - (OptionID, ProductCode)<BR><BR>So therefore I use the Options_ApplyTo table to link up the Products to its Options (a Many-to-one relationship). Here&#039;s my problem... for simplicity in my administration interface I have a virtual field that allows the user to enter a list of OptionIDs separated by commas. Whatever they enter there I sync up in the database through some scripts.<BR><BR>Well now I&#039;m trying to figure out how to EFFICIENTLY generate that same comma delimited list of OptionIDs when viewing the Products. For example a list of products should look like this:<BR><BR>ProductCode - ProductName - OptionIDs<BR>computer123 - AMD Laptop1 - 21, 44, 58, 19<BR>computer231 - AMD Laptop2 - 33, 12, 14<BR>computer333 - AMD Desktop - 88, 11, 18, 90, 17, 38<BR><BR><BR>When viewing a single product, this works excellent:<BR><BR>SQL = SQL & "DECLARE @TempList varchar(100) "<BR>SQL = SQL & "SELECT @TempList = &#039;&#039; "<BR>SQL = SQL & "SELECT @TempList = @TempList + &#039;, &#039; + CAST(OptionID AS varchar(100)) "<BR>SQL = SQL & "FROM Options_ApplyTo "<BR>SQL = SQL & "WHERE ProductCode = &#039;computer123&#039; "<BR>SQL = SQL & "SELECT @TempList = SUBSTRING(@TempList, 3, 8000) "<BR><BR>SQL = SQL & " SELECT Products.ProductCode, @TempList "<BR>SQL = SQL & " FROM Products WHERE ProductCode = &#039;computer123&#039;"<BR><BR>However I can&#039;t figure out how to make this same statement work to pull up ALL products, and their corresponding list of options.<BR><BR>Originally I needed this ability to work in MSAccess and SQL Server, but through my research thus far it looks like the only solution will be in SQL Server... what do you think?<BR><BR>Thank you guys SOOOOO much!

  2. #2
    Join Date
    Dec 1969

    Default RE: Virtual Comma Delimited List

    It is tough to do that in SQL and if you did.. it would definately not work in both SQL Server and Access. I think the best way to do it would just be to select the information and "roll it up" in your VB Script code.<BR><BR>Make your select return results like:<BR>computer123 - AMD Laptop1 - 21<BR>computer123 - AMD Laptop1 - 41<BR>computer123 - AMD Laptop1 - 50<BR>computer231 - AMD Laptop2 - 33<BR>computer231 - AMD Laptop2 - 12<BR><BR>Then loop through the results and build up the options by checking to see when the product changes. If you use GetRows I think you&#039;ll find this approach is sufficiently efficient.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts