Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Posts
    4

    Lightbulb Unanswered: how to select multiple rows as comma separated list?

    Hi,

    I have a table of users, a table of categories, and a many-to-many table linking users to categories.

    My problem is that I want to select all the users with an extra column containing a comma-separated list of the categories they belong to.

    Here is a stripped-down version of the table fields:

    tbl_User
    UserId, Email

    tbl_Category
    CatId, CatName

    tbl_User_Category
    UserId, CatId


    I have tried using the coalesce function to build a string, but can only get this to work for one row at a time:

    DECLARE @list nvarchar(100)

    SELECT @list = COALESCE(@list + ', ', '') + CAST(CatId AS varchar(4))
    FROM tbl_User_Category
    WHERE UserId = @UserId

    SELECT @list as List


    Any ideas on how to add to this to get it to do each row in tbl_Page? Or am I attacking this from the wrong angle?????

    Any help would be fantastic!

    thanks,
    Rob

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    A DBForums classic:

    create function dbo.UserCategories(@UserID as Whatever)
    returns varchar(500)
    as
    begin
    dim @DelimList as varchar(500)

    select @DelimList = isnull(@DelimList + ', ', '') + CatName
    from tbl_Category
    inner join tbl_User_Category on tbl_Category.CatID = tbl_User_Category.CatID
    where tbl_User_Category.UserID = @UserID

    return @DelimList
    end

    Use this function to return a comma-delimited list for any UserID. Unfortunately, you can't use this to get a list for every UserID in a single shot, so you will need to specify the function as an output field of your select statement. That means it will execute once for every UserID. If you have 100,000 UserIds you may need to solve your problem using a temporary table.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2004
    Posts
    4
    Thanks a lot!

    That's spot on.... except I'm using SQL Server 7 which doesn't have user defined functions! *curse*

    Could I replace the function with a stored procedure call? It doesn't like it if you try to exec a stored proc in a select statement though. Any way to get around this???

  4. #4
    Join Date
    Mar 2004
    Posts
    20
    Hi

    I'm not entirely sure This is what you are trying but faced with similar I find it easiest to work the llist in Excel with a new column with contents equating to the one above concatenated with the original row contents
    A B
    fred ="'" & A1 & "', "
    Bill = B1 & "'" & A1 & "', "
    Chaz = B1 & "'" & A1 & "', " This repeated for all subsequent rows

    So the last row would have 'Fred', 'Bill', 'Chaz', Whic you paste into an IN clause

    Gerry

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    1) You could upgrade.
    2) You could run the algorithm through a cursor for each row.
    3) You could upgrade.
    4) You could load your data into a temporary table and build your delimeted strings.
    5) You could upgrade.

    All these solutions would work, but if I were you I'd avoid methods 2 and 4 and select one of the remaining three.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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