Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565

    Unanswered: rowset values in a single string

    I am not close to an sql server today and this question was posed to me. can someone hook me up?

    " I want to query a column of values and place them into a single string seperated by commas" (as a function)

    Table a
    123
    456
    789
    321
    654
    987


    output
    123,456,789,321,654,987

    thanks in advance
    Last edited by Ruprect; 04-08-04 at 13:50.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hey-hey-hey, this forum actually holds a copyright on this algorythm. I don't remember who did it first (not me, I just got excited and drooled once I saw it)

    declare @str varchar(8000)
    set @str = ''
    select @str = @str + case when @str = '' then cast(field_name as varchar(25)) else ',' + cast(field_name as varchar(25)) end from [Table a]

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it might have been me or brettk, bringing it over from this article on sqlteam.com -- Using COALESCE to Build Comma-Delimited String
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    "rdjabarov or r937 " does string concantination

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by Ruprect
    "rdjabarov or r937 " does string concantination
    Egad! Enough with the "white noise" posts already!

    <RANT>
    Maybe I'm just grumpy because I'm busy, but right now I have very little tolerance for posts that serve no purpose whatsoever (except maybe to raise a particular person's post count).

    Technical posts are why I'm here. Courtesy posts (like thank yous) are Ok too, since they serve a useful purpose (indicating that the original poster is now a happy camper), as well as just being "the right thing to do" from a social perspective. Even a certain amount of horseplay is fun (lord knows, I jump into that too).

    The posts that do nothing but squander bandwidth annoy me though. It was bad enough when just one person was doing it, although they probably thought it was cute. Now that more people are starting to do it, the practice is really starting to annoy me.
    </RANT>

    -PatP

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Mea culpa, mea culpa, mea maxima culpa

    A working example....

    Again, this is PURE Theft...check out the link...

    Code:
    USE Northwind
    GO
    
     DECLARE @EmployeeList varchar(100)
    
      SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') 
    	  + CAST(EmployeeID AS varchar(5))
        FROM Employees
    ORDER BY EmployeeID
    
    SELECT @EmployeeList
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    i dont want to turn this into a flame so here are two observations

    1. some of us (omitting names so as not to get them in trouble)happen to have a cryptic thank you that displays itself as
    "so and so does X" this is just a combination of thank you and an aknowlegement of the poster's skill

    2. it's just so infectious.

    seriously, i see your point and maybe we can start special threads for just talking but i dont want to foster a hard assed community here, i want us all to enjoy this area and
    "a little nonsense now and then is cherished by the wisest men."

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by Ruprect

    seriously, i see your point and maybe we can start special threads for just talking
    Wish granted...

    OK....there are 2 wishes left now

    http://www.dbforums.com/showthread.p...51#post3658551
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hey, guys/gals (where the heck did I pick this Texan accent??? At the bar last night?)!!!
    It's Mr. Lindmand and I that have a grudge (goes a couple of months back), not you all!!! And a joke here and there is just fine. Especially if it's about Brett falling off the bar-stool...I mean his office chair

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by rdjabarov
    Especially if it's about Brett falling off the bar-stool...I mean his office chair
    Hey, no need to define my charcharter like that....

    ummmm defile...

    never mind......

    You still drunk?

    What else did you pick up at the bar last night...

    You know we should drag this over top the corral....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I thought this WAS the corral...man, I'll never drink again...the same combination...

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by Brett Kaiser
    Again, this is PURE Theft...check out the link...
    Did you mean Rudy's link, or did I miss yet another meeting ?!?!

    Sorry if I'm overly grumpy, I'm just stretched a bit thin on more than one front, and it seems to be catching up with me.

    As Ruprect pointed out, the comments are apparently infectious... They seem to have gotten me just under the skin! Sorry about that.

    -PatP

  13. #13
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Pat, you're cool, I knew it (Does it mean I'm going to the bar again? Maybe I need to pick a different bar...Brett, which one?)

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It's not even Mr. Lindman. Mr. Lindman has tried to play nice with Mr. Djabarov for the past few months, willing to let bygones be bygones, but Mr. Djabarob has insisted on jumping onto the ends of threads with snide and pointless comments, and Mr. Lindman's patience only goes so far.
    If it's not practically useful, then it's practically useless.

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

  15. #15
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hey, and look who's here?!!! Mr. Lindman at "the ends of threads with snide and pointless comments"!!!! Let me personally welcome you to "the end" of this thread

    EDITED: ...and did you ask anybody here if we give a rat's tail about your patience?

Posting Permissions

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