How to Display multiple records in 1 column

• IAK

  SSC-Addicted

  Points: 437

  More actions

  September 30, 2010 at 11:44 pm

  #227230

  i have table like this

  Poistion Name

  Manager Scott

  manager Jhon

  Manger adam

  Accountant marry

  Accountant zee

  and i want to display them like

  poistion name

  manager Scott, Jhon,Adam

  acountant marry,Zee

  Thansk

• Reo

  Hall of Fame

  Points: 3774

  More actions

  October 1, 2010 at 4:18 am

  #1230101

  USE <DatabaseName>

  Go

  CREATE TABLE EmpTable

  (Poistion Varchar(50), Name Varchar(50))

  GO

  INSERT EmpTable

  VALUES ('Manager', 'Scott')

  INSERT EmpTable

  VALUES('manager', 'Jhon')

  INSERT EmpTable

  VALUES('Manager','adam')

  INSERT EmpTable

  VALUES('Accountant','marry')

  INSERT EmpTable

  VALUES('Accountant','zee')

  GO

  -- Funcation

  CREATE FUNCTION [dbo].[fn_ReturnName]

  (@Poistion Varchar(50))

  RETURNS VARCHAR(MAX)

  AS

  BEGIN

  DECLARE @Name VARCHAR(MAX)

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

  FROM EmpTable WHERE Poistion=@Poistion

  ORDER BY Name

  RETURN @Name

  END

  GO

  --Sample Query 1

  SELECT DISTINCT Poistion + ' ' + dbo.fn_ReturnName(Poistion) As EmpName

  FROM EmpTable Order By 1 ASC

  --Sample Query 2

  SELECT DISTINCT Poistion

  ,dbo.fn_ReturnName(Poistion) As EmpName

  FROM EmpTable Order By Poistion ASC

  Ram
  MSSQL DBA

• IAK

  SSC-Addicted

  Points: 437

  More actions

  October 1, 2010 at 6:31 am

  #1230148

  No I did not try XML path....but its working ..thats really what I want..Bundle of thanks

• IAK

  SSC-Addicted

  Points: 437

  More actions

  October 1, 2010 at 6:33 am

  #1230149

  Thanks ..Its what I want ...I was thinking the whole day about this ..thanks