я вот так сделал:


select id1, text1, text2, comment1, comment2
from
(select top 100
(SELECT COUNT(*) FROM dbo.test_table) as r1,
id as id1,
text as text1,
comment as comment1
from dbo.test_table
order by id,text,comment) A
left join
(select top 100
(SELECT COUNT(*) FROM dbo.test_table) as r2,
id as id2,
text as text2,
comment as comment2
from dbo.test_table
order by id,text,comment) B
on
id1=id2 and r1<>r2



но результат не тот что задумывался

id text1 text2 comment1 comment2

1 aaa NULL aaa_comm NULL
1 bbb NULL bbb_comm NULL
2 asd NULL asd_comm NULL
2 ccc NULL ccc_comm NULL
3 ddd NULL ddd_comm NULL
4 eee NULL eee_comm NULL