今天的笔试题，求教sql语句
原表数据
rowval
1101
1102
21
现在需要显示成下面的形式
rowval
1101
1102
2101
2 102
求sql语句
[解决办法]
真恶心的题。
create table #t
(row int,
val int)
insert into #t
select 1 row,101 val
union all select 1,102
union all select 2,1
select distinct t3.row,t2.val from #t t1 join #t t2 on t1.val =t2.row,#t t3
select * from
(select distinct row from 表)a,
(select val from 表 where val!=1)b
create table fw
([row] int, val int)
insert into fw
select 1, 101 union all
select 1, 102 union all
select 2, 1
select [row],[val]
from
(select distinct [row] from fw) a
cross join
(select val from fw
where [row]=(select top 1 [row]
from fw
group by [row]
order by count(1) desc)
) b
order by a.[row],b.val
/*
row val
----------- -----------
1 101
1 102
2 101
2 102
(4 row(s) affected)
*/
SELECT a.row,b.val
FROM
(SELECT distinct row FROM tablename) a,
(SELECT val FROM tablename WHERE row !=2) b
