CREATE TABLE #GL(
actindx INT,
amnt NUMERIC (19,5),
ORDOCNUM VARCHAR(21)
)

CREATE TABLE #GL00100
(
	actindx INT,
	Actnumbr_2 VARCHAR(5),
	Actnumbr_3 VARCHAR(5)
)

CREATE TABLE #SOP10106
(
	SOPNUMBE  VARCHAR(21),
	Userdef1  VARCHAR(21)
)

CREATE TABLE #DATA (
	Userdef1 VARCHAR(21),
	JobASXCode VARCHAR(6),
	LASER     NUMERIC (19,5),
	MAIL        NUMERIC (19,5),
	MATERIAL     NUMERIC (19,5),
	PROGRAM      NUMERIC (19,5),
	EBIZ         NUMERIC (19,5),
	DESIGN       NUMERIC (19,5),
	LODGEMENT    NUMERIC (19,5),
	POSTAGE      NUMERIC (19,5),
	FREIGHT      NUMERIC (19,5)
)

INSERT INTO #DATA
SELECT #SOP10106.Userdef1, Job.JobASXCode, 
(CASE WHEN #GL00100.Actnumbr_2='14' then sum(amnt) else 0 end) as LASER,
(CASE WHEN #GL00100.Actnumbr_2='15' then sum(amnt) else 0 end) as MAIL,
(CASE WHEN #GL00100.Actnumbr_2='17' then sum(amnt) else 0 end) as MATERIAL,
(CASE WHEN #GL00100.Actnumbr_2='12' then sum(amnt) else 0 end) as PROGRAM,
(CASE WHEN #GL00100.Actnumbr_2='19' then sum(amnt) else 0 end) as EBIZ,
(CASE WHEN #GL00100.Actnumbr_2='11' then sum(amnt) else 0 end) as DESIGN,
(CASE WHEN (#GL00100.Actnumbr_2='04' then sum(amnt) else 0 end) as LODGEMENT,
(CASE WHEN (#GL00100.Actnumbr_2='04' then sum(amnt) else 0 end) as POSTAGE,
(CASE WHEN #GL00100.Actnumbr_2='03' then sum(amnt) else 0 end) as FREIGHT
FROM #GL 
LEFT JOIN #SOP10106 ON #GL.ORDOCNUM = #SOP10106.SOPNUMBE
LEFT OUTER JOIN Job on Job.JobNumber = #SOP10106.Userdef1
LEFT JOIN #GL00100 ON #GL.actindx=#GL00100.actindx
GROUP BY #SOP10106.Userdef1, Job.JobASXCode,#GL00100.Actnumbr_2
ORDER BY #SOP10106.Userdef1

DROP TABLE #GL
DROP TABLE #GL00100
DROP TABLE #SOP10106
DROP TABLE #DATA
GO