总结了一些有代表性的贴子，具体见http://expert.csdn.net/Expert/topic/2440/2440306.xml?temp=.6941645
1: 列转为行:
eg1:
Create table test (name char(10),km char(10),cj int)
go
insert test values(@#张三@#,@#语文@#,80)
insert test values(@#张三@#,@#数学@#,86)
insert test values(@#张三@#,@#英语@#,75)
insert test values(@#李四@#,@#语文@#,78)
insert test values(@#李四@#,@#数学@#,85)
insert test values(@#李四@#,@#英语@#,78)
想变成
姓名 语文 数学 英语
张三 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(不要用游标)
以前有相似的列子,现在找不到了,帮帮忙!
--1.创建一个合并的函数
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=@#@#
select @str=@str+@#,@#+cast(pid as varchar) from 表A where id=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go
--调用自定义函数得到结果
select distinct id,dbo.fmerg(id) from 表A
2:
/*********** 行转列 *****************/
测试：
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 @=@+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 @#+@+@# from t1@#)
a
-----------
15
9
1
0
1
2
2
0
文章来源于领测软件测试网 https://www.ltesting.net/
