總結(jié)了一些有代表性的貼子，具體見http://expert.csdn.net/expert/topic/2440/2440306.xml?temp=.6941645
1: 列轉(zhuǎn)為行:
eg1:
create table test (name char(10),km char(10),cj int)
go
insert test values('張三','語文',80)
insert test values('張三','數(shù)學(xué)',86)
insert test values('張三','英語',75)
insert test values('李四','語文',78)
insert test values('李四','數(shù)學(xué)',85)
insert test values('李四','英語',78)
想變成
姓名 語文 數(shù)學(xué) 英語
張三 80 86 75
李四 78 85 78
declare @sql varchar(8000)
set @sql = 'select name'
select @sql = @sql + ',sum(case km when '''+km+''' then cj end) ['+km+']'
from (select distinct km from test) as a
select @sql = @sql+' from test group by name'
exec(@sql)
drop table test
eg2:
有表a,
id pid
1 1
1 2
1 3
2 1
2 2
3 1
如何化成表b:
id pid
1 1,2,3
2 1,2
3 1
或者是從表b變成a(不要用游標(biāo))
以前有相似的列子,現(xiàn)在找不到了,幫幫忙!
--1.創(chuàng)建一個(gè)合并的函數(shù)
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @[email protected]+','+cast(pid as varchar) from 表a where [email protected]
set @str=right(@str,len(@str)-1)
return(@str)
end
go
--調(diào)用自定義函數(shù)得到結(jié)果
select distinct id,dbo.fmerg(id) from 表a
2:
/*********** 行轉(zhuǎn)列 *****************/
測(cè)試：
create table t1 (a int,b int,c int,d int,e int,f int,g int,h int)
insert t1 values(15, 9, 1, 0, 1, 2, 2, 0)
declare @ varchar(8000)
set @=''
select @[email protected]+rtrim(name)+' from t1 union all select ' from syscolumns where id=object_id('t1')
set @=left(@,len(@)-len(' from t1 union all select '))
--print @
exec('select '[email protected]+' from t1')
a
-----------
15
9
1
0
1
2
2
0
，歡迎訪問網(wǎng)頁設(shè)計(jì)愛好者web開發(fā)。
