Concatenation of rows in function

• am-244616

  SSCarpal Tunnel

  Points: 4936

  More actions

  November 15, 2007 at 1:45 pm

  #121115

  I have table - employeedescription which stores description of the employee. An employee can have multiple descriptions, below functions returns all the employeedescription for each employee, Is ther any way to concatenate all the descriptions for an employee into a single row

  empidempdescription

  _______________________________

  1Joined in 1971

  1Raise in 1976

  1change department in 1980

  output should be

  empidempdescription

  _______________________________

  1Joined in 1971 Raise in 1976 change department in 1980

  ALTER function [dbo].[F_GETDESC](@sk bigint) returns varchar(2000)

  as

  begin

  declare @desc varchar(2000)

  SELECT @desc= ltrim(rtrim(description))+ '...'

  FROM employeedescription

  WHERE employeedescription.key=@sk

  return @desc

  end

  Any suggestions and input should help

  Thanks

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  November 15, 2007 at 2:19 pm

  #751651

  AM -

  Jeff Moden and I were just having a bit of a conversation about that in another thread. He just posted an example that will work on a SQL 2000 box (which would work for you). It's on the top of page 6. He also goes into detail about the RIGHT and the WRONG way to use such a solution (which can go horribly wrong if you don't pay attention...)

  thread is here:

  Difference between != and <>

  ----------------------------------------------------------------------------------
  Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  November 15, 2007 at 2:59 pm

  #751664

  Actually, you can get to the precise post Matt is talking about by clicking on the following... not need to hunt pages...

  http://www.sqlservercentral.com/Forums/Topic419472-65-2.aspx#bm422799

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)