SELECT TOP 10 * FROM Product

WITH T AS
(
SELECT TOP 20 name,
       row_number() OVER (ORDER BY id) AS RN
FROM Products
ORDER BY id
)
SELECT
       MAX(CASE WHEN RN <=10 THEN name END) AS Col1,
       MAX(CASE WHEN RN > 10 THEN name END) AS Col2
FROM T
GROUP BY RN % 10

;WITH TopProducts AS
(
    SELECT
        ProductID, ProductName,
        ROW_NUMBER() OVER(ORDER BY --some-column-here-- DESC) 'RN'
    FROM dbo.Products
)
SELECT
    p1.ProductID, p1.ProductName,
    p2.ProductID, p2.ProductName
FROM
    TopProducts p1
CROSS JOIN
    TopProducts p2
WHERE
    p1.RN BETWEEN 1 AND 10        -- get rows 1-10 from the first CTE
    -- AND p2.RN BETWEEN 11 AND 20   redundant, as niktrs pointed out
    AND p1.RN + 10 = p2.RN        -- join rows from P1 and P2 so you don't get a cartesian product

select *
from
(
SELECT top 10 ROW_NUMBER() OVER(ORDER BY product) linenum, product
FROM products
) t1
 JOIN
(
SELECT top 20 ROW_NUMBER() OVER(ORDER BY product) linenum, product
FROM products
) t2 ON t1.linenum+10 = t2.linenum

SELECT *
FROM
(
    SELECT TOP 10 ROW_NUMBER() OVER(ORDER BY ColName) RowNo, ColName
    FROM TableName
) table1
INNER JOIN
(
    SELECT TOP 20 ROW_NUMBER() OVER(ORDER BY ColName) RowNo, ColName
    FROM TableName
) table2 ON table1.RowNo + 10 = table2.RowNo

SELECT * FROM Product LIMIT(10,10)