Modify SQL Code for

Hi all,

I'd like to modify the following code which creates a column for the multiple partners associated with each record. I would like to have the same for the Contact column. Some of these records may have multiple contacts and I would like them listed in a separate column.

USE [Encore]
GO
/****** Object:  StoredProcedure [dbo].[MCR]    Script Date: 02/11/2011 10:23:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[MCR]
AS
select tUser.FirstName +' '+ tUser.LastName as Partner, convert(varchar(100),tGenie.StartTime) AS StartTime, convert(varchar(100),tGenie.GenieNotes) AS GenieNotes, convert(varchar(100),tCompany.CompanyName) AS CompanyName, tPeople.FirstName +' '+ tPeople.LastName as Contact
into #tmp
from tGenie
LEFT JOIN tBusDev ON tGenie.BusDevGUID = tBusDev.GUID
LEFT JOIN tCompany ON tBusDev.CompanyGUID = tCompany.GUID
LEFT JOIN tBusDevPeople ON tGenie.BusDevGUID = tBusDevPeople.BusDevGUID
LEFT JOIN tPeople ON tBusDevPeople.PeopleGUID = tPeople.GUID
LEFT JOIN tBusDevUser ON tGenie.BusDevGUID = tBusDevUser.BusDevGUID
LEFT JOIN tUser ON tBusDevUser.UserGUID = tUser.GUID
WHERE tGenie.GenieTypeGUID = 'FB3E2527-4DFD-448B-A34D-9C3F737F4783' AND (StartTime > '2011-02-01 00:01:00.000' AND StartTime < '2011-02-11 00:01:00.000')

declare @sql nvarchar(max)  
declare @columns1 varchar(max)
declare @columns2 varchar(max)
declare @maxcount int

select @maxcount=MAX(c) from
(select COUNT(distinct partner) c
 from #tmp
 group by StartTime,GenieNotes,CompanyName,Contact) x

select
	@columns1 = coalesce(@columns1+',','') + '[' + CONVERT(varchar(10),number) + ']',
	@columns2 = coalesce(@columns2,'') + ',[' + CONVERT(varchar(10),number) + '] [Partner' + CONVERT(varchar(10),number) + ']'
from master..spt_values where type='p' and number between 1 and @maxcount

set @sql = '  
	SELECT StartTime, GenieNotes, CompanyName'+@columns2+', Contact
	FROM (SELECT *, RN=ROW_NUMBER() OVER (
		PARTITION BY StartTime, GenieNotes, CompanyName, Contact
		ORDER BY PARTNER)
		FROM (SELECT DISTINCT * FROM #TMP) A) B
	PIVOT (MAX([Partner]) FOR RN IN (' + @columns1 + ') ) AS pvt'  
EXEC (@sql);

Select allOpen in new window