sql行列转换 交叉表

例一:列转行

drop table test
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)

select * from test

想变成

姓名   语文   数学   英语
张三   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'
print @sql
exec(@sql) 

静态的:
select name,
sum(case km when '数学      ' then cj end) [数学      ],
sum(case km when '英语      ' then cj end) [英语      ],
sum(case km when '语文      ' then cj end) [语文      ]
from test group by name

例二:列转行

drop table t
create table t (cardno int,sex varchar(2),age int)
insert into t values (1,'男',12)
insert into t values (2,'男',10)
insert into t values (3,'女',14)
insert into t values (4,'女',18)
insert into t values (5,'男',20)

select * from t

动态的:
declare @sql varchar(8000)
set @sql='select a.id '
select @sql=@sql + ',sum(case a.sex when '''+ a.sex +''' then a.age end) ['+ a.sex+']'
from (select 1 as id,sex, avg(age) age from t group by sex) as a
select @sql = @sql + ' from (select 1 as id,sex,avg(age)age from  t group by sex) as a group by a.id'
print @sql
exec (@sql)

静态的:
select
sum(case a.sex when '男' then a.age end) [男],
sum(case a.sex when '女' then a.age end) [女]
from (select 1 as id,sex,avg(age)age from  t group by sex) as a
group by a.id

例三:行转列

drop table t1
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)

动态的:
select * from t1
declare @a varchar(8000)
set @a=''
select @a=@a+rtrim(name)+' from t1 union all select ' from syscolumns where id=object_id('t1')
set @a=left(@a,len(@a)-len(' from t1 union all select '))
print @a
exec('select '+@a+' from t1')

静态的:
select a from t1
union all
select b from t1
union all
select c from t1
union all select d from t1
union all
select e from t1
union all
select f from t1
union all
select g from t1
union all
select h from t1

例四:

有表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(不要用游标)
以前有相似的列子,现在找不到了,帮帮忙!

drop table t2
create table t2(id int,pid int)
insert into t2 values(1,1)
insert into t2 values(1,2)
insert into t2 values(1,3)
insert into t2 values(2,1)
insert into t2 values(2,2)
insert into t2 values(3,1)
select * from t2

--1.创建一个合并的函数
drop function fmerg
go
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+cast(pid as varchar) from t2 where id=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go

--2调用自定义函数得到结果
select distinct id,dbo.fmerg(id) from t2

例五:

drop table #t
create table #t(splb nvarchar(20),spm nvarchar(20),sl int,chr varchar(5))
insert #t select '水果','苹果',20,'10/1'
union all select '水果','桔子',10,'10/1'
union all select '水果','桔子',30,'10/2'
union all select '粮食','大米',12,'10/1'
union all select '粮食','麦子',25,'10/4'
select * from #t

动态的:
declare @sql nvarchar(4000)
select @sql=N'select splb,spm'
select @sql=@sql+',sum(case when chr='''+chr+''' then sl else 0 end) as ['+chr+']'
from #t group by chr
select @sql=@sql+' from #t group by splb,spm order by splb desc'
print @sql
exec (@sql)

静态的:
select splb,spm,
sum(case when chr='10/1' then sl else 0 end) as [10/1],
sum(case when chr='10/2' then sl else 0 end) as [10/2],
sum(case when chr='10/4' then sl else 0 end) as [10/4]
from #t group by splb,spm order by splb desc