SQL Server self join

I have a table as below:

table1

col1    col2    col3
1        A       1
2        B       1
3        A       2
4        D       2
5        X       3
6        G       3

Now can I get the result like below from above table. THe col2 in the below resultset is based on the col3 in table1 above. In above table1 col2, A and B have same id value in COL3 (i.e 1) so in the result set we just separate it in new columns and so on. A and D have same id COL3 (i.e 2) and X and G have same id in COL3 (i.e 3) in above table1. ANyone can write a sql query to get the following result.

   col1   col开发者_如何学Go2    
     A      B 
     A      D
     X      G

SELECT
  col1 = t.col2,
  col2 = t2.col2
FROM table1 t
  INNER JOIN table1 t2 ON t.col3 = t2.col3 AND t.col1 < t2.col1

SELECT
  t1.col2 as col1,
  t2.col2
FROM Table1 t1
INNER JOIN Table1 t2 on t1.col3 = t2.col3
WHERE t1.col1 > t2.col1

If you are on SQL Server 2005 or later:

WITH ranked AS (
  SELECT
    *,
    rn = ROW_NUMBER() OVER (PARTITION BY col3 ORDER BY col2)
  FROM table1
)
SELECT
  col1 = r1.col2,
  col2 = r2.col2
FROM ranked r1
  INNER JOIN ranked r2 ON r1.col3 = r2.col3
WHERE r1.rn = 1
  AND r2.rn = 2

select
    a.col2 as "col1",
    b.col2 as "Col2"
from
    table1 a
    join table1 b on a.col3 = b.col3

With some assumptions on the table structure, i.e. there exists exactly 2 entries in col3 for every unique value in col3.

DECLARE @table1 TABLE([col1] int, [col2] varchar, [col3] int);
INSERT INTO @table1(col1, col2, col3) VALUES(1, 'A', 1);
INTO @table1(col1, col2, col3) VALUES(2, 'B', 1);
INSERT INTO @table1(col1, col2, col3) VALUES(3, 'A', 2);
INSERT INTO @table1(col1, col2, col3) VALUES(4, 'D', 2);
INSERT INTO @table1(col1, col2, col3) VALUES(5, 'X', 3);
INSERT INTO @table1(col1, col2, col3) VALUES(6, 'G', 3);

SELECT
(SELECT TOP(1) t1.[col2] FROM @table1 AS t1 WHERE t1.[col3] = g.[GroupId] ORDER BY t1.[col1] ASC) AS [a],
(SELECT TOP(1) t2.[col2] FROM @table1 AS t2 WHERE t2.[col3] = g.[GroupId] ORDER BY t2.[col1] DESC) AS [b]
FROM
(SELECT DISTINCT u.col3 AS [GroupId] FROM @table1 AS u) AS g