Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94

    Unanswered: concatenated string of comma separated values (was "Help with Query")

    I have following 2 queries which return different results.

    Code:
    declare @accountIdListTemp varchar(max) 
    SELECT COALESCE(@accountIdListTemp + ',','') + CONVERT(VARCHAR(10),acct_id)
      FROM (SELECT Distinct acct_id
      FROM SomeTable) Result
    print @accountIdListTemp
    The above query return the values without concatenating it.

    Code:
    declare @pot_commaSeperatedList  varchar(max)
    
    SELECT DISTINCT acct_id
    into #accountIdListTemp
      FROM SomeTable
    
    
    SELECT @pot_commaSeperatedList = COALESCE(@pot_commaSeperatedList + ',','') + CONVERT(VARCHAR(100),acct_id)
      FROM #accountIdListTemp
    print @pot_commaSeperatedList 
    drop table #accountIdListTemp
    This query returns result as concatenated string of comma separated values.

    If i want to get similar result in a single query how can i get it?
    In GOD we believe. Everything else we Test!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can drop your temp table in favor of a subquery, but you will still need a separate statement to print the results.
    If you really must do this as a single statement (are you trying to create a view?), then you will need to create a function to build the concatenated string and call the function from your statement.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Nov 2006
    Location
    UK
    Posts
    46
    I think that "Print" is less important think in this query
    He just doesn’t use “select” twice.
    Regards
    Kris Zywczyk

  4. #4
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94
    Kris you have written similar query to what i've furnished.

    I just want to know why the first query of mine did not work?
    In GOD we believe. Everything else we Test!

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Perhaps you should read Brett's post.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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