[AccessD] Multi-value field format

I've done this once or twice, but it is best to use a junction table to
store the productID and CategoryID.


I know this is most likely Access that he is doing this in, but I do have a
UDF for MS SQL that will take a muti-value field and return a table:

CREATE FUNCTION [dbo].[udfListToTable] (@HList VarChar(1000), @Delimiter
CHAR(1))
RETURNS @ListTable TABLE (Mystr VARCHAR(20))
AS
BEGIN
    --Purpose: To convert a Comma delimited text to a Temp Variable table
To help avoid dynamic sql
    --         Instead you can join the temp table or use it in your where
clause if a field is IN the subquery
    DECLARE @Mystrtext as VarChar(20)

    IF RIGHT(RTRIM(@HLIST),1) <>@Delimiter
        SET @HList = @HList + @Delimiter

    WHILE CHARINDEX(@Delimiter, @HList) > 0
    BEGIN
        IF CHARINDEX(@Delimiter, @HList) > 0
           BEGIN
            SELECT @Mystrtext =LEFT(@HList, CHARINDEX(@Delimiter, @HList)-1)
           END
        ELSE
           BEGIN
             SELECT @Mystrtext = RTRIM(LTRIM(@HList))
           END
        --Insert into Variable Table
        INSERT INTO @ListTable(Mystr)
               SELECT RTRIM(LTRIM(@Mystrtext))
        --Remove Item from list
        SELECT @HList = RIGHT(RTRIM(@HList), LEN(RTRIM(@HList)) -
CHARINDEX(@Delimiter, @HList))
    END
    RETURN
END

--Call in this manner:
--SELECT * FROM dbo.udfListToTable('jim,joe,bob,tom',',')
/*
results:
jim
joe
bob
tom

*/

On Fri, May 11, 2012 at 10:23 AM, Susan Harkins <ssharkins at gmail.com> wrote:

> A reader is considering the multi-value field for the following reason:
>
> "I have to format the data in this way as a last step to export to the
> e-commerce platform.  As I import a CSV table into the platform, it
> requires stuff like product name, sku, price, weight, etc.  It also has a
> field for category.  A product can belong to multiple categories, and the
> way the it imports/exports is to fill that field with the category name,
> separated by semicolons, in the one field.  A product can be a member of up
> to 20 categories (all separated by semicolons)."
>
> I'd like to steer him away from the multi-value field. I know combining
> field values into a single line is a common request but I've never had to
> do it myself -- any suggestions?
>
> Thanks!
> Susan H.
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>