Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Posts
    15

    Unanswered: combining multiple rows in 1 row

    how can i get a 1 row result set having multiple rows joined into 1 row

    if i have 1 column having 5 rows i want to use a select statement that selects all rows joined into 1 row (results seperated by a comma for example)

    thx
    samham

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Want to show us the query? What would make them join together?
    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.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    He wants to combine multiple rows..

    And you don't use COALESCE to build a comma delimited srting..

    Is used so that any null value in the string does not blow away the results...

    It's the ability to do SELECT @x = @X + col1

    like...

    Code:
    DECLARE @x varchar(8000)
    SELECT @x = ISNULL(@x,'') + ISNULL(FirstName,'')  FROM Employees
    SELECT @x
    The coalesec trick allows you to eliminmate commas if the value in the column is Null

    so you dont get 1,,2,3,4,,5
    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.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    He wants to combine multiple rows
    yes, he wants the values from multiple rows to be put into a comma-delimited string
    And you don't use COALESCE to build a comma delimited srting
    damned straight on that one, i certainly don't, i would never do it that way -- in fact, i would probably just never do it
    The coalesec trick allows you to eliminmate commas if the value in the column is Null
    yes, that's correct, that's what it does for the first row

    rudy

  6. #6
    Join Date
    Jan 2003
    Posts
    15
    thx guys that's exactly what i wanted

    i'll use the code from the article


    DECLARE @EmployeeList varchar(100)

    SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') +
    CAST(Emp_UniqueID AS varchar(5))
    FROM SalesCallsEmployees
    WHERE SalCal_UniqueID = 1

    SELECT @EmployeeList
    --Results--

    ---------
    1, 2, 4

Posting Permissions

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