CREATE FUNCTION [dbo].[getShowContactsForDepartment_AsString] 
(
	@ShowNumber varchar(8), @ProductionDepartmentId integer
)
RETURNS varchar(500)
AS
BEGIN

	DECLARE @names VARCHAR(500)

	SET @names = ''

	SELECT 
		@names = @names + ', ' + NameInitials + Case WHEN ScheduleCommentShort Is Null THEN '' ELSE ' ' + ScheduleCommentShort End
	FROM 
		vw_ShowContacts 
	WHERE 
		ShowNumber = @ShowNumber AND ProductionDepartmentId = @ProductionDepartmentId
	ORDER BY
		Id
	
	--Drop the last comma
	IF Len(@names) > 0 
	BEGIN
		SET @names = Right(@names,LEN(@names)-1)
	End
	
	RETURN @names

END


GO

CREATE FUNCTION [dbo].[GroupContactsByDepartment_InitialsOnly]
(	
	@ShowNumber varchar(8)
)
RETURNS TABLE 
AS
RETURN 
(
SELECT 
	@ShowNumber as ShowNumber,
	dbo.getShowContactsForDepartment_AsString(@ShowNumber, 1) as Depart1,
	dbo.getShowContactsForDepartment_AsString(@ShowNumber, 2) as Depart2,
	dbo.getShowContactsForDepartment_AsString(@ShowNumber, 3) as Depart3,
	dbo.getShowContactsForDepartment_AsString(@ShowNumber, 4) as Depart4,
	dbo.getShowContactsForDepartment_AsString(@ShowNumber, 5) as Depart5,
	dbo.getShowContactsForDepartment_AsString(@ShowNumber, 6) as Depart6,
	dbo.getShowContactsForDepartment_AsString(@ShowNumber, 7) as Depart7,
	dbo.getShowContactsForDepartment_AsString(@ShowNumber, 8) as Depart8,
	dbo.getShowContactsForDepartment_AsString(@ShowNumber, 9) as Depart9,
	dbo.getShowContactsForDepartment_AsString(@ShowNumber, 10) as Depart10,
	dbo.getShowContactsForDepartment_AsString(@ShowNumber, 11) as Depart11,
	dbo.getShowContactsForDepartment_AsString(@ShowNumber, 13) as Depart12,
	dbo.getShowContactsForDepartment_AsString(@ShowNumber, 14) as Depart13,
	dbo.getShowContactsForDepartment_AsString(@ShowNumber, 15) as Depart14,
	dbo.getShowContactsForDepartment_AsString(@ShowNumber, 17) as Depart15,
	dbo.getShowContactsForDepartment_AsString(@ShowNumber, 18) as Depart16
)

GO

CREATE VIEW [dbo].[vw_ShowContacts_AllContactsByDepartmentInitialsOnly]
AS
SELECT 
	C.* 
FROM
	ShowHeaders as SH
OUTER APPLY GroupContactsByDepartment_InitialsOnly(SH.ShowNumber) AS C;


GO

CREATE FUNCTION [dbo].[getShowContactsForDepartment_AsString] 
(
        @ShowNumber varchar(8), @ProductionDepartmentId integer
)
WITH SCHEMABINDING  -- get just a little bit more performance out of this
RETURNS TABLE 
AS
SELECT ShowNumber = @ShowNumber,
       ProductionDepartmentID = @ProductionDepartmentID,
       STUFF((
        SELECT 
                ', ' + NameInitials + Case WHEN ScheduleCommentShort Is Null THEN '' ELSE ' ' + ScheduleCommentShort End
        FROM 
                vw_ShowContacts 
        WHERE 
                ShowNumber = @ShowNumber AND ProductionDepartmentId = @ProductionDepartmentId
        ORDER BY
                Id
        FOR XML PATH('')),1,1,'')
GO