IF EXISTS (SELECT id From tempdb.dbo.sysobjects o Where	o.xtype in ('U') And o.id = object_id(N'tempdb..#Curr_Dates'))
		DROP TABLE [dbo].[#Curr_Dates]
			CREATE TABLE #Curr_Dates (ColumnId varchar(50))
DECLARE @TableUnitAllocation SYSNAME

		SELECT	@TableUnitAllocation = 'dbo.CuMe_Curr_Unit_Allocation'
DECLARE @SQLUnitAllocation NVARCHAR(MAX)
		SELECT	@SQLUnitAllocation ='
		SELECT	Distinct 2 Id, '''' Office_Allocation_Year, Unit_Allocation_Id Booking_Link_Alloc_Id, ''Total Saleable'' Booking_Link_Program_Id,
				''Total Saleable'' Booking_Link_Program_Id, ''''
				Booking_Link_OverBook_Id, '''' Booking_Link_Default, '''' Office_Allocation_Batch_Id, '''' Office_Allocation_Upd_Date,
				 value,
				Null OtherValue,
				Convert(Varchar(10), Cast(Substring(Replace(Code, ''Unit_Allocation_'', Unit_Allocation_Year), 1, 4) + ''-'' + SUBSTRING(REPLACE(Code, ''Unit_Allocation_'', Unit_Allocation_Year), 5, 2) + ''-'' + SUBSTRING(REPLACE(Code, ''Unit_Allocation_'', Unit_Allocation_Year), 7, 2) As Datetime), 6) Code,
				3 TypeId
		FROM dbo.CuMe_Curr_Unit_Allocation
		UNPIVOT (
			Value FOR Code IN (' + STUFF((
			SELECT ', [' + c.name + ']'
			FROM sys.columns c WITH(NOLOCK)
			LEFT JOIN (
				SELECT i.[object_id], i.column_id
				FROM sys.index_columns i WITH(NOLOCK)
				WHERE i.index_id = 1
			) i ON c.[object_id] = i.[object_id] AND c.column_id = i.column_id
			WHERE c.[object_id] = OBJECT_ID(@TableUnitAllocation)
				AND i.[object_id] IS NULL
				And c.Name In (Select 'Unit_Allocation_' + ColumnId From dbo.[#Curr_Dates])
				--Ignore these Columns
				And c.Name Not In ('Unit_Allocation_Id', 'Unit_Allocation_Office_Id', 'Unit_Allocation_Club', 'Unit_Allocation_Resort', 'Unit_Allocation_Unit_Type', 'Unit_Allocation_Date', 'Unit_Allocation_Year', 'Unit_Allocation_Batch_Id', 'Unit_Allocation_Upd_Date')
			ORDER BY c.Name
			FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ''
print @SQLUnitAllocation
EXEC(@SQLUnitAllocation)