SQL动态行列倒转

需要使用pivot 语法如下：

以下是带批注的 PIVOT 语法。

SELECT <非透视的列>,

    [第一个透视的列] AS <列名称>,

    [第二个透视的列] AS <列名称>,

    ...

    [最后一个透视的列] AS <列名称>,

FROM

    (<生成数据的 SELECT 查询>)

    AS <源查询的别名>

PIVOT

(

    <聚合函数>(<要聚合的列>)

FOR

[<包含要成为列标题的值的列>]

    IN ( [第一个透视的列], [第二个透视的列],

    ... [最后一个透视的列])

) AS <透视表的别名>

<可选的 ORDER BY 子句>;

创建数据库的SQL语句：

create database T69
on
(
name='T69_data',
filename='I:\C#练习\T69_data.mdf',
size=10,
filegrowth=20%
)
log on
(
name='T69_log',
filename='I:\C#练习\T69_log.ldf',
size=3,
maxsize=20,
filegrowth=10%
)
go

use T69
go
create table Score
(
   id int primary key identity(1,1),
   [sid] int not null,
   Java int ,
   [Sql] int 
)

go

create table Minzu
(
    id int primary key identity(1,1),
    name varchar(50) not null

)

go
create table Student
(
   id int primary key identity(1,1),
   name varchar(20) not null,
   age int not null,
   mzId int not null,
   
)

go
alter table score
add constraint fk_score_sid foreign key ([sid]) 
references student(id)

alter table student
add constraint fk_student_mzid foreign key ([mzid]) 
create database T69

go
use T69
go
create table Score
(
   id int primary key identity(1,1),
   [sid] int not null,
   Java int ,
   [Sql] int 

)

go

create table Minzu
(
    id int primary key identity(1,1),
    name varchar(50) not null

)

go
create table Student
(
   id int primary key identity(1,1),
   name varchar(20) not null,
   age int not null,
   mzId int not null,
   
)

go
alter table score
add constraint fk_score_sid foreign key ([sid]) 
references student(id)

alter table student
add constraint fk_student_mzid foreign key ([mzid]) 
references minzu(id)
go

insert into Minzu values('维吾尔族')
insert into Minzu values('汉族')
insert into Minzu values('苗族')
insert into Minzu values('藏族')
insert into Minzu values('朝鲜族')
insert into Minzu values('俄罗斯族')
go
insert into Student values('a',20,2)
insert into Student values('b',25,2)
insert into Student values('c',29,3)
insert into Student values('d',20,1)
insert into Student values('e',20,1)
insert into Student values('f',30,1)
insert into Student values('g',35,1)
insert into Student values('h',33,1)
insert into Student values('i',35,3)
insert into Student values('j',18,4)
insert into Student values('k',19,4)
insert into Student values('l',17,4)
insert into Student values('m',40,1)
insert into Student values('n',41,1)
insert into Student values('o',38,1)
insert into Student values('p',27,1)
insert into Student values('q',26,1)
insert into Student values('r',24,5)
insert into Student values('s',24,1)
go
insert into Score values(1,50,60)
insert into Score values(2,89,70)
insert into Score values(3,88,80)
insert into Score values(4,90,45)
insert into Score values(5,90,95)
insert into Score values(6,65,75)
insert into Score values(7,70,60)
insert into Score values(8,72,73)
insert into Score values(9,80,45)
insert into Score values(10,90,100)
insert into Score values(11,100,60)
insert into Score values(12,35,60)
insert into Score values(13,null,60)
insert into Score values(14,null,null)

静态的：

select * from (select Student.mzId,Minzu.name
from Student inner join 
	Minzu on Student.mzId=Minzu.id) p
	pivot(count(mzId) for name in(汉族,苗族,维吾尔族,藏族,朝鲜族,俄罗斯族)) t

此SQL语句可以得出所有民族：

SELECT     case Minzu.id when Minzu.id then Minzu.name else null end
FROM        dbo.Minzu 

动态的：

begin
declare @sql varchar(8000) 
select @sql=isnull(@sql+',','')+name  FROM  dbo.Minzu group by name

set @sql='
SELECT * from(SELECT     dbo.Student.mzId, dbo.Minzu.name
FROM         dbo.Student INNER JOIN
                      dbo.Minzu ON dbo.Student.mzId = dbo.Minzu.id) p
pivot(count(mzId) for name in('+@sql+')) t'

exec(@sql)

end