SQL数据库列行转换

[yuzao]

如何有ＳＯＬ语句查询
a b c
7 2 3
转换成
name values
a 7
b 2
c 3

[pt1314917]

--用动态：
create table os(a  int,b int,c int)
insert into os select 7, 2,  3 

declare @sql varchar(8000)
set @sql=''
select @sql=@sql+'union all select name='''+ name +''',value='+name+ ' from os ' from syscolumns where id=object_id('os')
set @sql=stuff(@sql,1,10,'')
exec(@sql)

[-狙击手-]

[Quote=引用 3 楼 ojuju10 的回复:]

select name='a',[values]=a from tab
union all
select name='b',[values]=b from tab
union all
select name='c',[values]=c from tab
[/Quote]

[yyyyzzzz_2002]

/*如何有ＳＯＬ语句查询 
a b c 
7 2 3 
转换成 
name values 
a     7 
b     2 
c     3*/

use tempdb
go
if object_id('tempdb..#') is not null
drop table #

create table #(a int
				,b int
				,c int)
insert into #
select 7,2,3
select * from #

select name,[values]
from (select a,b,c from # )  as a
unpivot
([values] for [name]
		in (a,b,c)
) as unpvt

[wzy_love_sly]

[Quote=引用 5 楼 pt1314917 的回复:]
SQL code--用动态：createtableos(aint,bint,cint)insertintoosselect7,2,3declare@sqlvarchar(8000)set@sql=''select@sql=@sql+'union all select name='''+name+''',value='+name+'from os'fromsyscolumnswhereid=object_id('os')set@sql=stuff(@sql,1,10,'')exec(@sql)
[/Quote]

pt哥写的不错..

[决策分析104]

/*如何有ＳＯＬ语句查询
a b c
7 2 3
转换成
name values
a 7
b 2
c 3*/

/*如何有ＳＯＬ语句查询
a b c
7 2 3
转换成
name values
a 7
b 2
c 3*/

create table zz
(a int,
b int,
c int)

insert into zz values(7,2,3)

select * from zz

---本来很简单,不要想的那么复杂..考虑问题,希望简单化,不要想的那么复杂..
---平平淡淡的语句,就能完成很复杂的工作,前提要考虑语句的执行是否会给数据库造成很大压力.

[pengxuan]

学习

[sweetweiwei]

转个帖给你,上面说得很详细哦
http://topic.csdn.net/u/20080326/15/fd734772-ed54-4882-a7eb-07a86afd050e.html

[正宗老冉]

学习！

[ojuju10]

select name='a',[values]=a from tab
union all
select name='b',[values]=b from tab
union all
select name='c',[values]=c from tab

[wangxuelid]

declare @t table (a  int,b int,c int)
insert into @t select 7, 2,  3 

select name='a',[values]=a from @t 
union all 
select name='b',[values]=b from @t 
union all 
select name='c',[values]=c from @t 
/*
a	7
b	2
c	3
*/