如何将字段以记录的形式列出？具体看内实例

[klmycity3000]

现在有一个表：报纸价格表paper_price
字段为：
ID,1price,2price,3price,4price,5price,6price,7price
内容为：
1,100000,20000,3400000,348753,882121,392832,200000
2,120000,23000,3000000,218753,802121,312832,320000
......

如果我想搜索所有字段中大于400000的记录，以记录集形式，并可分页
如：
ID price price_day
1, 3400000 3price
1，882121 5price
2, 3000000 3price
2, 802121
......

怎么写呢，给个思路吧，谢谢

[gc_ding]

行列转换，参考：
--通过增加新列将行列完全互换

create table tb1(经办人 varchar(20),台次 int,比率 decimal(9,2),总金额 decimal(9,2))
insert into tb1(经办人,台次,总金额,比率) values('张三',20,20000,0.2)
insert into tb1(经办人,台次,总金额,比率) values('李四',80,80000,0.8)

select * from tb1
select id=identity(int,1,1),* into #T from tb1
select * from #T

declare @s1 varchar(8000),@s2 varchar(8000) ,@s3 varchar(8000),@s5 varchar(8000) ,@i varchar(10)
select @s1='',@s2='',@s3='',@s5='',@i='0'
select
@s1=@s1+',@'+@i+' varchar(8000)'
,@s2=@s2+',@'+@i+'=''id='''''+name+''''''''
,@s3=@s3+' select @'+@i+'=@'+@i+'+'',[''+cast(id as varchar)+'']=''''''+cast(['+name+'] as varchar)+'''''''' from #T'
,@s5=@s5+'+'' union all select ''+@'+@i
,@i=cast(@i as int)+1
from Tempdb..syscolumns
where object_id('Tempdb..#T')=id and name not in('id')
order by colid
select @s1=substring(@s1,2,8000)
,@s2=substring(@s2,2,8000)
,@s5=substring(@s5,14,8000)
print @s1
print @s2
print @s3
print @s5
--print 'declare '+@s1+' select '+@s2+@s3+' exec('''+@s5+')'
exec('declare '+@s1+' select '+@s2+@s3+' exec('''+@s5+')')

drop table #T
drop table tb1

--结果
/*
经办人 台次 比率 总金额
-----------------------------------
张三 20 .20 20000
李四 80 .80 80000

id 经办人 台次 比率 总金额
--------------------------------------------
1 张三 20 .20 20000
2 李四 80 .80 80000

id 1 2
-------------------------------
经办人 张三 李四
台次 20 80
比率 0.2 0.8
总金额 20000 80000
*/

[marco08]

create table T(ID int,[1price] int,[2price] int,[3price] int,[4price] int,[5price] int,[6price] int,[7price] int)
insert T select 1,100000,20000,3400000,348753,882121,392832,200000
union all select 2,120000,23000,3000000,218753,802121,312832,320000

select * from
(
select ID, price=[1price], price_day='1price' from T
union all
select ID, price=[2price], price_day='2price' from T
union all
select ID, price=[3price], price_day='3price' from T
union all
select ID, price=[4price], price_day='4price' from T
union all
select ID, price=[5price], price_day='5price' from T
union all
select ID, price=[6price], price_day='6price' from T
union all
select ID, price=[7price], price_day='7price' from T
)tmp
where price>400000
order by ID

--result
ID price price_day
----------- ----------- ---------
1 3400000 3price
1 882121 5price
2 802121 5price
2 3000000 3price

(4 row(s) affected)

[marco08]

--簡化一下

create table T(ID int,[1price] int,[2price] int,[3price] int,[4price] int,[5price] int,[6price] int,[7price] int)
insert T select 1,100000,20000,3400000,348753,882121,392832,200000
union all select 2,120000,23000,3000000,218753,802121,312832,320000

select ID, price=[1price], price_day='1price' from T
where [1price]>400000
union all
select ID, price=[2price], price_day='2price' from T
where [2price]>400000
union all
select ID, price=[3price], price_day='3price' from T
where [3price]>400000
union all
select ID, price=[4price], price_day='4price' from T
where [4price]>400000
union all
select ID, price=[5price], price_day='5price' from T
where [5price]>400000
union all
select ID, price=[6price], price_day='6price' from T
where [6price]>400000
union all
select ID, price=[7price], price_day='7price' from T
where [7price]>400000
order by ID

--result
ID price price_day
----------- ----------- ---------
1 3400000 3price
1 882121 5price
2 802121 5price
2 3000000 3price

(4 row(s) affected)