DECLARE @WorkCode VARCHAR(25)
SET @WorkCode='61163201328'
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL DROP TABLE #Temp
CREATE TABLE #Temp(WorkId VARCHAR(50),TLabourAmt decimal(18,2),TMaterialAmt decimal(18,2)
)
DELETE FROM #Temp
INSERT INTO #Temp SELECT WME.workID,ISNULL(SUM(WME.labourAmt_1),0),ISNULL(SUM(WME.materialAmt_1),0)
FROM dbo.nregs_worksMeasurement WME INNER JOIN dbo.nregs_worksMaster WM ON WM.workID=WME.workID
WHERE WM.measurementCount=1
GROUP BY WME.workID
INSERT INTO #Temp SELECT WME.workID,ISNULL(SUM(WME.labourAmt_1),0),ISNULL(SUM(WME.materialAmt_1),0)
FROM dbo.nregs_worksMeasurement WME INNER JOIN dbo.nregs_worksMaster WM ON WM.workID=WME.workID
WHERE WM.measurementCount=2
GROUP BY WME.workID
INSERT INTO #Temp SELECT WME.workID,ISNULL(SUM(WME.labourAmt_1),0),ISNULL(SUM(WME.materialAmt_1),0)
FROM dbo.nregs_worksMeasurement WME INNER JOIN dbo.nregs_worksMaster WM ON WM.workID=WME.workID
WHERE WM.measurementCount=3
GROUP BY WME.workID
INSERT INTO #Temp SELECT WME.workID,ISNULL(SUM(WME.labourAmt_1),0),ISNULL(SUM(WME.materialAmt_1),0)
FROM dbo.nregs_worksMeasurement WME INNER JOIN dbo.nregs_worksMaster WM ON WM.workID=WME.workID
WHERE WM.measurementCount=4
GROUP BY WME.workID
SELECT * FROM #Temp order by WorkId
Here i am using temp table for inserting based on measurementCount