sql select a field into 2 columns

I am trying to run below 2 queries on the same table and hoping to get results in 2 different columns.

Query 1: select ID as M from table where field = 1 returns:

1
2
3

Query 2: select ID as N from table where field = 2 returns:

4
5
6

My goal is to get

Column1 - Column2
-----------------
1            4
2            5
3            6

Any suggestions? I am using SQL Server 2008 R2

Thanks

There has to be a primary key to foreign key relationship to JOIN data between two tables.

That is the idea about relational algebra and normalization. Otherwise, the correlation of the data is meaningless.

http://en.wikipedia.org/wiki/Database_normalization

The CROSS JOIN will give you all possibilities. (1,4), (1,5), (1, 6) ... (3,6). I do not think that is what you want.

You can always use a ROW_NUMBER() OVER () function to generate a surrogate key in both tables. Order the data the way you want inside the OVER () clause. However, this is still not in any Normal form.

In short. Why do this?

Quick test database. Stores products from sporting goods and home goods using non-normal form.

The results of the SELECT do not mean anything.

-- Just play
use tempdb;
go

-- Drop table
if object_id('abnormal_form') > 0
drop table abnormal_form
go

-- Create table
create table abnormal_form
(
    Id int,
    Category int,
    Name varchar(50)
);

-- Load store products
insert into abnormal_form values
(1, 1, 'Bike'),
(2, 1, 'Bat'),
(3, 1, 'Ball'),
(4, 2, 'Pot'),
(5, 2, 'Pan'),
(6, 2, 'Spoon');

-- Sporting Goods
select * from abnormal_form where Category = 1

-- Home Goods
select * from abnormal_form where Category = 2

-- Does not mean anything to me
select Id1, Id2 from
(select ROW_NUMBER () OVER (ORDER BY ID) AS Rid1, Id as Id1 
 from abnormal_form where Category = 1) as s
join
(select ROW_NUMBER () OVER (ORDER BY ID) AS Rid2, Id as Id2 
 from abnormal_form where Category = 2) as h
on s.Rid1 = h.Rid2

We definitely need more information from the user.