Reading a table left-right instead of up-down

[awhitsel]

I have a table with two fields. The first field is a "record number" and the second field is a separate "catalog number".

I want to be able to view the items from the second field across so that they are in a separate field, so that there is a unique "record number" in each record with the "catalog numbers" read from left to right.

If you don't understand what I'm asking about, look below:

My records currently look like this:

REC_NUM CAT_NUM
----------- -----------
1 1
1 2
2 3
2 4
2 5
3 6
4 7
4 8

I want to be able to read my table like this:

REC_NUM CAT_NUM1 CAT_NUM2 CAT_NUM3
----------- ------------- ------------- -------------
1 1 2
2 3 4 5
3 6
4 7 8

I tried doing this in Access, and it went haywire.

Is there a way that this can be done in a SQL Server query?

Any help would be greatly appreciated.

Thanks.

 

[ClaireHCM]

select Rec_num,
(case when rec_num = rec_num then min(cat_num) end )as Cat_NUM1,
(case when rec_num = rec_num and (min(cat_num)+1)<=max(cat_num) then min(cat_num)+1 end)as Cat_NUM2,
(case when rec_num = rec_num and (min(cat_num)+2)<=max(cat_num) then min(cat_num)+2 end)as Cat_NUM3

from mytable group by rec_num