Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Dec 2003
    Posts
    2

    Unanswered: Concatenating Columns

    Here is what i am trying to do:

    SELECT Hotel.Name, Hotel.Address1 + ' ' + Hotel.City + ' ' + Hotel.State
    FROM Hotel INNER JOIN Room
    ON Hotel.HotelID = Room.HotelID"

    That returns
    Name Address
    ----------------- -----------------------------------------------
    Comfort Suites 6110 Peachtree Dunwoods Rd Atlanta GA

    I don't know if this is possible or not, but what I want the output to look like is

    Name Address
    ----------------- -----------------------------------------------
    Comfort Suites 6110 Peachtree Dunwoods Rd
    Atlanta GA

    The atlata GA should be directly underneath 6110 Peachtree

    I am running this sql statement from an aspx page for a website. I have basic knowledge of sql, but not a lot, especially looking at the sql statements with the @ symbol

    if someone knows how or knows if this is even possible it would be a big help

  2. #2
    Join Date
    Sep 2003
    Posts
    212
    Well the @ symbol means that it is a variable so eg @temp would be a variable. and u can use varible in where cluases etc.

    what do u mean by: "The atlata GA should be directly underneath 6110 Peachtree"??
    shud be on a different row or ?!?!

  3. #3
    Join Date
    Dec 2003
    Posts
    2
    well instead of the query returning everything on one line i can add a carriage return somewhere so the city and state are directly below the address, but still in the same row, I have no idea if this is even possible

    so the return would look like

    Address Name Details
    --------------------------- ----------------------- ------------------------
    Peachtree Dunwoods Rd Comfort Suites 6110 1 King Bed Suite69.00
    Atlanta, GA

    And this would be all one row
    Last edited by Deltwebguy; 12-11-03 at 12:53.

  4. #4
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Concatenating Columns

    SELECT ' ', Hotel.Name, Hotel.Address1 + ' ' + Hotel.City
    FROM Hotel
    INNER JOIN Room ON Hotel.HotelID = Room.HotelID
    UNION
    SELECT ' ', Hotel.Name, Hotel.State
    FROM Hotel
    INNER JOIN Room ON Hotel.HotelID = Room.HotelID
    ORDER BY 2, 1


    it will give you and extra column with nothing in it
    but the rest is OK

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can add a carriage return or any other ASCII character using the CHAR() function. Like this:

    select 'abc' + char(13) + 'def'

    But you should consider passing the various portions of the address as separate values and letting your report application format them. Formatting is best handled by the interface, not the database engine.

    blindman

  6. #6
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    i've tried it

    the "retum chariot" doesn't put the date on the other line

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Since the data is being returned to a web page, the formatting should be done there. Should be easy enough. Return the city/state in one column, then display that on the web page below the address. Maybe

    rs open query, conn

    <TR><TD><%=rs("name")%><TD><%=rs("address")%></TR>
    <TR><TD><TD><%=rs("citystate")%></TR>
    rs.movenext

    OK. This is more VB6, but VB.NET has to have similar constructs...

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "retum chariot"?

    It works if you output in text, not grid.

    blindman

  9. #9
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    "carriage return" = "retour chariot"

    I don't know the japaneese word for it

  10. #10
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    I'm going for a 3 day Weekend
    The Grand Poobah won't have to correct my mistakes for 3 days

    Good vacation mister BlindMan !

  11. #11
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Concatenating Columns

    SELECT '1', Hotel.Name, Hotel.Name, Hotel.Address1 + ' ' + Hotel.City
    FROM Hotel
    INNER JOIN Room ON Hotel.HotelID = Room.HotelID
    UNION
    SELECT '2', Hotel.Name,'', Hotel.State
    FROM Hotel
    INNER JOIN Room ON Hotel.HotelID = Room.HotelID
    ORDER BY 2, 1


    How could we substract the 2 first unusefull columns from this query ?

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Japaneese? We speak hillbilly and redneck here in the midwest.

    blindman

  13. #13
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    was only a joke...
    and I don't know it in polish too

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Ok. We don't really speak hillbilly and redneck.

    Y'all.

    blindman

  15. #15
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    I know, no indians left there to teach you

    (last unusefull post here)

Posting Permissions

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