declare @s table(phone_one int, phone_two int, phone_three int)
insert into @s select 1,2,3select phone_one from @s
union all
select phone_two from @s
union all
select phone_three from @s/*
phone_one
-----------
1
2
3(3 行受影响)*/
insert into @s select 1,2,3select phone_one from @s
union all
select phone_two from @s
union all
select phone_three from @s/*
phone_one
-----------
1
2
3(3 行受影响)*/
--SQL2005实现方法:
select
*
from
A
unpivot
(num for col in([phone_one],[phone_two],[phone_three]))T2
)tmp
select phone_one as phone_one from yourtable
union
select phone_two as phone_one from yourtable
union
select phone_three as phone_one from yourtable
简单。=======================================
powerbuilder11&SQL QQ群请加:6539042
phone_one1
from
@s
unpivot
(phone_one1 for col in([phone_one],[phone_two],[phone_three]))
T2
应该这个最简单了.
--
select
num
from
(select
*
from
A
unpivot
(num for col in([phone_one],[phone_two],[phone_three]))T2
)tmp
from 表
union all
select phone_two
from 表
union all
select phone_three
from 表如果字段是不定的,可用动态语句生成
应该做成动态的,如果列很多,老是输入相同的东西很腻的
if object_id('tb') is not null
drop table tb
go
create table tb(phone_one int,phone_two int,phone_three int)
insert into tb select 1,2,3 declare @sql varchar(1000)
select @sql=isnull(@sql,'select ')+a.name+' from tb union all select '
from (select * from syscolumns where id=object_id('tb')) a
set @sql=substring(@sql,1,len(@sql)-16)
exec(@sql)接分了