CONCENTATION OF ROWS BY USING COALESCE

• tmahaboobbasha

  Grasshopper

  Points: 12

  More actions

  October 14, 2009 at 3:29 am

  #210953

  Hi

  We Can concenate Rows by using the COALESCE Statement as follows.

  For example if we want to concenate the Name data as "Ram,Shyam,Basha,Shiva" from Emp table.

  CREATE TABLE #Emp(ID INT, Name VARCHAR(40))

  INSERT #Emp SELECT 1, 'Ram'

  INSERT #Emp SELECT 2, 'Shyam'

  INSERT #Emp SELECT 3, 'Basha'

  INSERT #Emp SELECT 4, 'Shiva'

  IDName

  1Ram

  2Shyam

  3Basha

  4Shiva

  DECLARE @EmplList VARCHAR(400)

  SELECT @EmpList = COALESCE(@EmpList,'')+

  CASE WHEN COALESCE(@EmpList,'') <> '') AND COALESCE(Name,'') <> '' THEN ',' ELSE '' END +

  COALESCE(Name,'') FROM #Emp Order BY ID

  SELECT @EmpList

  DROP TABLE #Emp

  Result

  Ram,Shyam,Basha,Shiva