DBCC UPDATEUSAGE and sp_updatestats

Two ways to do this
1. Using a function

create function dbo.ConcatList ( @id int )
returns varchar(8000)
as
begin
declare @res varchar(8000)
select @res = coalesce(@res + ',', '') + yourfield
from yourtable
where somefield = @id
return (@res)
end


and use it like this:

select id, dbo.ConcatList(id) as list
from yourtable
group by id






2. Using temp tables

the problem with the above method is that, if the Table is a temp table, then the function wont be able to access this table, in such case you can follow this method




SET NOCOUNT ON
Create table #temp (EmployeeID int, Email varchar(100), DepartmentName varchar(100))
INSERT INTO #Temp SELECT 1234, 'Bob@test.com', 'sales'
INSERT INTO #Temp SELECT 1234, 'Bob@test.com', 'Marketing'
INSERT INTO #Temp SELECT 1222, 'Sue@test.com', 'Support'
INSERT INTO #Temp SELECT 1222, 'Sue@test.com', 'Support'

Create Table #T (Id int identity(1,1), EmployeeId int , Email varchar(100), Depts Varchar(1000))
Insert #T (EmployeeId , Email )
SELECT DISTINCT EmployeeId, Email FROM #temp

Declare @ID int, @Depts varchar(500)

Select @ID=1

While @ID <=(Select Max(ID) from #T )
Begin
SET @Depts =NULL
Select @Depts=COALESCE (@Depts+',','')+ DepartmentName
from #temp
Inner Join #T ON #T.EmployeeId=#temp.EmployeeId and #T.Email=#temp.Email
Where #T.ID=@ID
GROUP BY #T.EmployeeId,#temp.EmployeeId , #T.Email,#temp.Email,#T.ID,DepartmentName
Update #T Set Depts=@Depts--Left(@Depts,Len(@Depts)-1)
WHERE ID = @Id
Select @ID=@ID+1
End

Select * from #T