交叉表的SQL写法

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
=====================================================================
table1:
ID WareID WareName
1 001 材料1
2 002 材料2
table2:
ID IndexID Pdate P1 P2 P3 P4 Pn1 Pn2 Pn3 Pn4
1 1 2005-01-01 A B C D 1 2 3 4
2 1 2005-02-02 B A D C 3 4 2 1
3 2 2005-03-04 A C D B 4 4 2 1
要得到下面的结果：
ID WareID WareName 2005-01-01 2005-02-02 2005-01-01 2005-02-02 2005-03-04 2005-03-04
1 001 材料1 ABCD BADC 1234 3421
2 002 材料2 ACDB 4421
写法：
create table #table1(id int,wareid char(3),warename varchar(20))
create table #table2(id int,indexid int,pdate datetime,p1 varchar(20),p2 varchar(20),p3 varchar(20),p4 varchar(20),
pn1 varchar(20),pn2 varchar(20),pn3 varchar(20),pn4 varchar(20))

insert into #table1
select 1,'001','材料1'
union all select 2,'002','材料2'
insert into #table2
select 1, 1,'2005-01-01','A','B','C','D','1','2','3','4'
union all select 2,1,'2005-02-02','B','A','D','C','3','4','2','1'
union all select 3,2,'2005-03-04','A','C','D','B','4','4','2','1'

declare @sql varchar(8000),@sql2 varchar(8000)
select @sql=''
select @sql=@sql+',max(case pdate when '''+convert(varchar(10),pdate,120)+''' then p1+p2+p3+p4 else NULL end) as '''+convert(varchar(10),pdate,120)+''''
from #table2
group by indexid,pdate
select @sql2=''
select @sql2=@sql2+',max(case pdate when '''+convert(varchar(10),pdate,120)+''' then pn1+pn2+pn3+pn4 else NULL end) as '''+convert(varchar(10),pdate,120)+''''
from #table2
group by indexid,pdate

--print 'select a.*'+@sql+@sql2+' from #table1 a, #table2 b where a.id=b.indexid group by a.id,a.wareid,a.warename'
exec('select a.*'+@sql+@sql2+' from #table1 a,#table2 b where a.id=b.indexid group by a.id,a.wareid,a.warename')