Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Feb 2004
    Location
    Fairfax, VA
    Posts
    16

    Question Unanswered: Is this possible......???

    Say I have a table with one field and there are 26 records in it... A, B, C, D, E, ... etc.

    If I do a select statement on that table I get 26 records, one for each field. (select fieldName from tblName)

    Is it possible to set a variable equal to a single string off of the select statement and delimit it with a chosen delimiter (ie "A,B,C,D,E,F.......")

    Thank You for the help!!!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Is this possible......???

    With which DBMS? There is no standard SQL answer to this.

  3. #3
    Join Date
    Feb 2004
    Location
    Fairfax, VA
    Posts
    16
    Using MS SQL 7

  4. #4
    Join Date
    Feb 2004
    Location
    Fairfax, VA
    Posts
    16
    Looking for something like

    set @stringName = (select fieldName & ',' from tblName)

    So that @stringName is set to a string 'A,B,C,D,E,.....'
    Last edited by gman_gsxr750; 02-02-04 at 11:09.

  5. #5
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    What about this?

    drop table test
    create table test(id int identity,code varchar(10))
    go
    insert test(code) values('a')
    insert test(code) values('b')
    insert test(code) values('c')
    insert test(code) values('d')
    insert test(code) values('e')
    go
    declare @str varchar(8000)
    set @str=''
    select @str=@str+code from test
    select @str

  6. #6
    Join Date
    Feb 2004
    Location
    Fairfax, VA
    Posts
    16
    Originally posted by snail
    What about this?

    drop table test
    create table test(id int identity,code varchar(10))
    go
    insert test(code) values('a')
    insert test(code) values('b')
    insert test(code) values('c')
    insert test(code) values('d')
    insert test(code) values('e')
    go
    declare @str varchar(8000)
    set @str=''
    select @str=@str+code from test
    select @str
    That's pretty much what I'm looking for but I don't understand how your '+ code from test' is going to work. That piece should be my recordset

    set @str = @str + (select fieldName from tblName)

    something like that where my recordset can be turned into a string.

  7. #7
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by gman_gsxr750
    That's pretty much what I'm looking for but I don't understand how your '+ code from test' is going to work. That piece should be my recordset

    set @str = @str + (select fieldName from tblName)

    something like that where my recordset can be turned into a string.
    Just try and you'll see...

  8. #8
    Join Date
    Feb 2004
    Location
    Fairfax, VA
    Posts
    16
    Originally posted by snail
    Just try and you'll see...
    Holy moley!!! I've never seen that before!!!

    Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you!

    One last question (only because I've never used the code in that way before...

    can I put a conditional on it

    set @str = @str + code from table (where id < 100)

    or something like that?

    And did I mention...... Thank you!

  9. #9
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by gman_gsxr750
    Holy moley!!! I've never seen that before!!!

    Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you!

    One last question (only because I've never used the code in that way before...

    can I put a conditional on it

    set @str = @str + code from table (where id < 100)

    or something like that?

    And did I mention...... Thank you!
    Why not?

  10. #10
    Join Date
    Feb 2004
    Location
    Fairfax, VA
    Posts
    16
    Originally posted by snail
    Why not?
    Ever get that rush when something finally goes your way and things work out?

    Thank you soooooooooo much! I got the conditional to work as well. I just need to play with this a little to figure out the nuances.

    Do you know what that kind of query is called so I can reference?

  11. #11
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by gman_gsxr750
    Ever get that rush when something finally goes your way and things work out?

    Thank you soooooooooo much! I got the conditional to work as well. I just need to play with this a little to figure out the nuances.

    Do you know what that kind of query is called so I can reference?
    I have no idea...

  12. #12
    Join Date
    Feb 2004
    Location
    Fairfax, VA
    Posts
    16
    Originally posted by snail
    I have no idea...
    OK, last question, hopefully you can help me with this.

    Here's my code:
    declare @idpeople int
    set @idpeople = 200002
    declare @str varchar(8000)
    set @str = ''

    select @str = @str + ',' + ideventcode from tblPeopleEvents where idpeople = @idpeople

    let's say this returns the following ',CXL,AS' (two codes CXL and AS)

    this works fine, but if I add an order by clause to it (so it returns AS,CXL instead), I only get one of the two values (CXL)

    any ideas on how far I can take the select portion (where, order by, group by, etc)?

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it's called a magic query

    it works, and it produces the result by magic

    hey snail, where's the comma between values?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Feb 2004
    Location
    Fairfax, VA
    Posts
    16
    OK, here's my final code. I used a subquery to get the result set the way I needed it. Much thanks to Snail for the help. And to r937 for the sarcasm .

    create procedure spGetEventString
    @idpeople int,
    @eventString varchar(255) OUTPUT

    as

    set @eventString = ''

    -- Create string of Event codes
    -- use sub query to order result set
    select @eventString = @eventString + rTPE.ideventcode
    from
    (
    select top 100 idEventCode + ',' as idEventCode
    from tblPeopleEvents
    where idpeople = @idpeople
    order by idEventCode
    ) as rTPE

    --Remove Trailing Comma
    set @eventString = left(@eventString,len(@eventString)-1)

    return

  15. #15
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by r937
    it's called a magic query

    it works, and it produces the result by magic

    hey snail, where's the comma between values?

    I am not a magician I am only learning...
    Last edited by snail; 02-02-04 at 12:05.

Posting Permissions

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