How to iterate through data from select statement using while loop and put them into temp table

See more:

SQL-Server

stored-procedure

Hello,

i am not that pro in SQL, i got stuck at one point and can't figure a way out of this. i am trying to get data for one report. i have created a query for my stored procedure which is like this .

SELECT dbo.tblWorkHistory.WeekEndingDate,dbo.tblWorkHistory.WorkerID, 
        dbo.tblTitles.Title + ' ' + dbo.tblWorkers.Forename + ' '  + dbo.tblWorkers.Surname AS WorkerName,
        dbo.tblClients.ClientName,dbo.tblWorkHistory.WorkDate,
        dbo.tblJobCategories.JobCategory,dbo.tblRateTypes.RateType,
        dbo.tblWorkHistoryRates.Hours,dbo.tblWorkHistoryRates.ChargeRate,
        dbo.tblClients.ClientID,dbo.tblBookings.CostCentreID,dbo.tblSites.SiteID,dbo.tblSiteRateContracts.JobCategoryID,
        dbo.tblWorkHistory.BranchID,dbo.tblSystemBranches.BranchLocation,dbo.tblWorkHistoryRates.RateTypeID,dbo.tblCostCentres.CostCentre, 
        dbo.tblSystemBranches.OwnerID, tblWorkHistory.StartTime, tblWorkHistory.FinishTime, tblWorkHistory.BreakHours,
        tblWorkHistoryRates.Hours * tblWorkHistoryRates.ChargeRate AS Charge
FROM    dbo.tblWorkHistory LEFT OUTER JOIN
        dbo.tblBookings ON dbo.tblWorkHistory.BookingID = dbo.tblBookings.BookingID LEFT OUTER JOIN
        dbo.tblSiteRateContracts ON dbo.tblBookings.SiteRateContractID = dbo.tblSiteRateContracts.SiteRateContractID LEFT OUTER JOIN
        dbo.tblSites ON dbo.tblSiteRateContracts.SiteID = dbo.tblSites.SiteID LEFT OUTER JOIN
        dbo.tblClients ON dbo.tblSites.ClientID = dbo.tblClients.ClientID LEFT OUTER JOIN
        dbo.tblJobCategories ON dbo.tblSiteRateContracts.JobCategoryID = dbo.tblJobCategories.JobCategoryID LEFT OUTER JOIN
        dbo.tblCostCentres ON dbo.tblBookings.CostCentreID = dbo.tblCostCentres.CostCentreID LEFT OUTER JOIN
        dbo.tblWorkers ON dbo.tblWorkHistory.WorkerID = dbo.tblWorkers.WorkerID LEFT OUTER JOIN
        dbo.tblTitles ON dbo.tblWorkers.TitleID = dbo.tblTitles.TitleID LEFT OUTER JOIN
        dbo.tblSystemBranches ON dbo.tblWorkHistory.BranchID = dbo.tblSystemBranches.BranchID LEFT OUTER JOIN
        dbo.tblSystemOwner ON dbo.tblSystemBranches.OwnerID = dbo.tblSystemOwner.OwnerID LEFT OUTER JOIN
        dbo.tblWorkHistoryRates ON dbo.tblWorkHistory.WorkHistoryID = dbo.tblWorkHistoryRates.WorkHistoryID LEFT OUTER JOIN
        dbo.tblRateTypes ON dbo.tblWorkHistoryRates.RateTypeID = dbo.tblRateTypes.RateTypeID
WHERE   (dbo.tblWorkHistory.InvoiceID IS NULL) AND (dbo.tblSiteRateContracts.IsDailyRate = 1) 
        AND (dbo.tblWorkHistory.Completed = 1)
        AND (dbo.tblWorkHistoryRates.Hours IS NOT NULL)
        AND (dbo.tblClients.BranchID = 9)
        AND (dbo.tblSystemBranches.OwnerID = 3

but because the database is normalized it returns multiple rows. for instance a worker has worked for one client on one date and he has done basic and overtime hours. now basic and overtime are rate types and there is 1 to many relationship between client and ratetype hence i am getting two rows for this worker, what i want is that it should display one row for worker's such work history and in one row it display basic as one column and overtime as another column.

i don't have much idea on how i can use while loop inside stored procedure and iterate through the result of this select statement? i don't want to use cursor so if you guys can please help me here will be really great full

What I have tried:

i tried using pivot but because i want to display hours, pay rate and charge rate columns it is not working proper.