问一个简单的行列转换的问题吧

[mulpig]

看了csdn上的好多，还是不太理解
亲历亲为下。。
表：
a b c d
1 2 3 4

转换成
a 1
b 2
c 3
d 4 (其中a是主键)

[mulpig]

哎呀，不好弄么？
那要怎么办呢？
可以加个where条件，就查出来一笔数据，这样会不会好弄点呢？？？
库里就一笔数据这样好转换么？？

[mulpig]

还有，是sql2000

[xiaoyu19039]

create table #t
(
a varchar(10)
,b varchar(10)
,c varchar(10)
,d varchar(10)
)
insert into #t
select 1,2,3,4
union select 5,6,7,8
declare @sql Nvarchar(4000)
set @sql = 'select ''id'''
select @sql = @sql+ ',sum(case a when ' + a + ' then '+a+' end) '--[' + Subject + ']'
from (select distinct a from #t) as y
set @sql = @sql + ' from #t '
exec (@sql)
drop table #t
我只转换了一列，其余的你照着转换吧

[hkdeaccp]

路过,学习!

[wzy_love_sly]

create table tb(a int,b int,c int,d int)
insert into tb select 1,2,3,4
insert into tb select 5,6,7,8

select max(case when id='1' then a end) as [1],max(case when id='2' then a end) as [2] from (select row_number() over(order by a) as id,* from tb)t
union all
select max(case when id='1' then b end) as [1],max(case when id='2' then b end) as [2] from (select row_number() over(order by a) as id,* from tb)t
union all
select max(case when id='1' then c end) as [1],max(case when id='2' then c end) as [2] from (select row_number() over(order by a) as id,* from tb)t
union all
select max(case when id='1' then d end) as [1],max(case when id='2' then d end) as [2] from (select row_number() over(order by a) as id,* from tb)t

1 5
2 6
3 7
4 8

动态不好弄，我试试

[sunday_lookforyou]

学习

[wzy_love_sly]

create table tb(a varchar(20),b varchar(20),c varchar(20),d varchar(20))
insert into tb select '1','2','3','4'
insert into tb select 'a','b','c','d'

select max(case when id='1' then a end) as [1],max(case when id='2' then a end) as [2] from (select row_number() over(order by a) as id,* from tb)t
union all
select max(case when id='1' then b end) as [1],max(case when id='2' then b end) as [2] from (select row_number() over(order by a) as id,* from tb)t
union all
select max(case when id='1' then c end) as [1],max(case when id='2' then c end) as [2] from (select row_number() over(order by a) as id,* from tb)t
union all
select max(case when id='1' then d end) as [1],max(case when id='2' then d end) as [2] from (select row_number() over(order by a) as id,* from tb)t

1 a
2 b
3 c
4 d

[mulpig]

真厉害，哈哈，我试试哦
我不要动态的，不动的就好啦

[mulpig]

你误解我意思了
表里面是：
套餐类型 基本资费 长途资费 入网政策
aa bb cc dd

然后我想要转换成：
套餐类型 aa
基本话费 bb
长途资费 cc
入网政策 dd

[wzy_love_sly]

drop table tb
go
create table tb(a varchar(20),b varchar(20),c varchar(20),d varchar(20))
insert into tb select '1','2','3','4'
insert into tb select '5','6','7','8'

declare @i int,@sqlresult varchar(8000)
set @sqlresult=''
set @i=1 
while @i<=(select count(1) from syscolumns where id=object_id('tb'))
begin
declare @sql varchar(8000),@colname varchar(50)
select @colname=name from syscolumns where id=object_id('tb') and colid=@i
select @sql=isnull(@sql+',','')+' max(case when id='''+ltrim(id)+''' then '+@colname+' end) as [第'+ltrim(id)+'列]'
from(select row_number() over(order by a) as id,* from (select * from tb union all select 'a','b','c','d')t)t
set @sql='select '+@sql+' from (select row_number() over(order by a) as id,* from (select * from tb union all select ''a'',''b'',''c'',''d'')t)t union all '
set @sqlresult=@sqlresult+@sql
set @sql=null
set @i=@i+1
end
set @sqlresult = left (@sqlresult,len(@sqlresult)-10)
exec(@sqlresult)

第1列 第2列 第3列
1 5 a
2 6 b
3 7 c
4 8 d

好难写

[xiaoyasheng]

a b c d是列名吗

[weiweir]

在程序里吗？可以用DataList绑定的。

[mulpig]

a b c d是列名

[Adechen]

在sql server 2005中有一个函数,可以实现表的行列转换,函数名记不太清楚了

[sxmonsy]

这里有个文章写的就是你想要的.http://bbs.zdnet.com.cn/thread-187670-1-1.html

[wzy_love_sly]

呵呵，这是列转行，有点不好弄

行转列是
表：
a 1
b 2
c 3
d 4
转换成

a b c d
1 2 3 4