declare @StartDate datetime set @StartDate=GETDATE() declare @BedCount int -- Get the number of beds in the bed table set @BedCount=(select COUNT(*) from Beds where isactive=1) -- Get a list of the beds in the Bed Table declare @Beds table (BedID int, BedDesc varchar(20), BedPointer int IDENTITY ) insert into @Beds (BedID,BedDesc) select bedid,dbo.Beds.BedDescription from dbo.Beds where isactive=1 order by BedDescription -- Start our Temp Table Create Table #MyBedsByDate ( DateField varchar(10)) -- Add the beds as columns in the temp table 1 addl column for each row in the bed table declare @NewColumn varchar(50)='' -- Variable to hold the new column name declare @mySql varchar(300) -- Variable to hold the update statement declare @RowCtr int -- Variable for Loop Counter set @RowCtr=1 -- Initilize the Loop Counter -- Loop through temp table and add a column for each bed in the bedtable while (@RowCtr<=@BedCount) begin -- Get the new column name from the bed table set @NewColumn=(Select BedDesc from @Beds where BedPointer=@RowCtr) -- Get the set @mySQL='alter table #MyBedsBydate add '+@NewColumn+' varchar(20)' exec(@mySQL) set @RowCtr=@RowCtr+1 end set @RowCtr=1 declare @DateValueToInsert varchar(10) ------------------------------------------------------------------------- -- Insert Row For Each Date ------------------------------------------------------------------------- While (@RowCtr<=30) begin set @DateValueToInsert=CONVERT(varchar(10),@StartDate,101) insert into #MyBedsByDate (DateField) values (@DateValueToInsert) set @StartDate=DATEADD(dd,1,@StartDate) set @RowCtr=@RowCtr+1 end ------------------------------------------------------------------------- -- Get The Results ------------------------------------------------------------------------- select * from #MyBedsByDate ------------------------------------------------------------------------- -- Drop the Temp Table ------------------------------------------------------------------------- drop table #MyBedsByDate