课程表
CREATE TABLE [dbo].[demo_Course]( [Cid] [uniqueidentifier] NOT NULL, [CourseName] [varchar](50) NULL, CONSTRAINT [PK_demo_Course] PRIMARY KEY CLUSTERED ( [Cid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
学员表
CREATE TABLE [dbo].[demo_Student]( [Sid] [uniqueidentifier] NOT NULL, [Pid] [uniqueidentifier] NULL, [StudentName] [varchar](50) NULL, [JoinDate] [datetime] NULL, [Sex] [int] NULL, [Old] [int] NULL, CONSTRAINT [PK_demo_Student] PRIMARY KEY CLUSTERED ( [Sid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
成绩表
CREATE TABLE [dbo].[demo_Score]( [Sid] [uniqueidentifier] NOT NULL, [Cid] [uniqueidentifier] NOT NULL, [Score] [int] NOT NULL ) ON [PRIMARY]
行转列
Declare @sql varchar(8000) Set @sql = 'Select Sid' Select @sql = @sql + ',max(case CourseName when '''+CourseName+''' then Score else 0 end) ['+CourseName+']' from demo_Course as cj --把所有唯一的科目的名称都列举出来 Select @sql = @sql+' from (select s.sid , c.CourseName , s.Score from demo_Course c left join demo_Score s on c.Cid = s.Cid) t group by Sid' print (@sql)
上面这段输出的SQL如下:
Select Sid,max(case CourseName when '数学' then Score else 0 end) [数学],
max(case CourseName when '英语' then Score else 0 end) [英语],
max(case CourseName when '语文' then Score else 0 end) [语文] from
(select s.sid , c.CourseName , s.Score from demo_Course c left join demo_Score s on c.Cid = s.Cid) t group by Sid