SET NOCOUNT ON - Jian Table Create Table Course (ID INT, Course NVARCHAR (10), Professional VARCHAR (8)) Insert Into Course SELECT 1, 'Course 1', 'Professional 1' Union All Select 2, 'Course 2 ',' Professional 1'Union All SELECT 3, 'Course 3', 'Professional 1'
Create Table transcript (student NVARCHAR (10), K1 INT, K2 INT, K3 INT, professional varchar (8)) Insert Into transcript Select 'student number 1', 20, 30, 50, 'professional 2' union all SELECT 'Learn 2', 30, 40, 60, 'Professional 1'Union All Select' Study 3 ', 90, 80, 100,' Professional 1 '
- Query SELECT * FROM program table Select * from transcript
Declare @sql nvarchar (4000) DECLARE @ 专 v (8) set @ 专 = 'Professional 1'Declare @Count Intselect @ count = count (*) from course WHERE professional = @ professional set @ SQL =' SELECT number ' Declare @i tent @i = 1
While @i <= @ count begin select @ SQL = @ SQL ', K' CAST (@i as nvarchar) 'AS' ' course ' '' 'from course table where id = @ i set @ i = @ i 1 end
Exec (@SQL 'from transcript WHERE professional =' '' @ 专业 '' ')
- Delete DROP TABLE CUMMENT DROP TABLE Table / * Query Results ID Course -------------------------- 1 course 1 Professional 12 Course 2 Professional 13 Lesson 3 Professional 1
Learn K1 K2 K3 Professional ---------- --------------------------------- ------- Study No. 1 20 30 50 Professional 2 Study 2 30 40 60 Professional 1 Student 3 90 80 100 Professional 1
