动态SQL行转列语法

课程表

1. CREATE TABLE [dbo].[demo_Course](
2. [Cid] [uniqueidentifier] NOT NULL,
3. [CourseName] [varchar](50) NULL,
4. CONSTRAINT [PK_demo_Course] PRIMARY KEY CLUSTERED
5. (
6. [Cid] ASC
7. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
8. ) ON [PRIMARY]

学员表

1. CREATE TABLE [dbo].[demo_Student](
2. [Sid] [uniqueidentifier] NOT NULL,
3. [Pid] [uniqueidentifier] NULL,
4. [StudentName] [varchar](50) NULL,
5. [JoinDate] [datetime] NULL,
6. [Sex] [int] NULL,
7. [Old] [int] NULL,
8. CONSTRAINT [PK_demo_Student] PRIMARY KEY CLUSTERED
9. (
10. [Sid] ASC
11. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
12. ) ON [PRIMARY] 

成绩表

1. CREATE TABLE [dbo].[demo_Score](
2. [Sid] [uniqueidentifier] NOT NULL,
3. [Cid] [uniqueidentifier] NOT NULL,
4. [Score] [int] NOT NULL
5. ) ON [PRIMARY]

行转列

1. Declare @sql varchar(8000)
2. Set @sql = 'Select Sid'
3. Select @sql = @sql + ',max(case CourseName when '''+CourseName+''' then Score else 0 end) ['+CourseName+']'
4. from demo_Course as cj --把所有唯一的科目的名称都列举出来
5. 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'
6. print (@sql)

上面这段输出的SQL如下：

1. 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