We have a table which looks like the following...

Rep_det
=========

Job_num Rep_num Dim1 Dim2 Dim3 Met1 Met2 Met3
------- -------- ---- ---- ---- ---- ---- ----
100 1 AK 200 300
100 1 CA 500 200
100 1 NY 1000 350
100 1 MA 750 250
200 1 CA 250 350
200 1 NY 1000 700
200 1 MA 500 100
200 1 NJ 750 1100

WE WANT A REPORT AS SHOWN BELOW...

STATE CID SID PRIOR_CID PRIOR_SID
----- --- --- --------- ---------
AK 200 300
cA 250 350 500 200
NY 1000 700 1000 350
MA 500 100 750 250
NJ 750 1000

If you would notice that AK is not present in current "Job" so current "CID" / "SID" is NULL but it was present in the previous "Job" so there are entries in PRIOR_CID / PRIOR_SID.

Its opposite for NJ.

Could anyone help me out with a query for the same. Would really appreciate a help. I have attched the above so that you have a look.

Thanks.

The output in your post was hard to understand so I'm a little unsure the below suggestion is what you're looking for:

SELECT dim1 STATE, MAX(dim2) CID, MAX(dim3) SID,
MAX(met1) PRIOR_CID, MAX(met2) PRIOR_SID
FROM table
GROUP BY dim1

The above assumes using MA as an example that the data in the table is this:

Job_num Rep_num Dim1 Dim2 Dim3 Met1 Met2
100 1 MA 750 250
200 1 MA 500 100

The purpose of the MAX in the SELECT is to collapse null/value.

If this is not the route you meant, my I suggest using a spreadsheet to accurately show the data to columns in the table rather than a notepad document.