CREATE DATABASE tam
GO
USE tam
GO
CREATE TABLE Tbl1(HoTen NVARCHAR(50), MonHoc NVARCHAR(50), Diem INT)
GO
INSERT INTO Tbl1(HoTen, MonHoc, Diem) VALUES('A', 'Toan', 8)
INSERT INTO Tbl1(HoTen, MonHoc, Diem) VALUES('A', 'Van', 6)
INSERT INTO Tbl1(HoTen, MonHoc, Diem) VALUES('B', 'Toan', 7)
INSERT INTO Tbl1(HoTen, MonHoc, Diem) VALUES('B', 'Van', 5)
GO
SP
GO
DECLARE @CauLenhTruyVan NVARCHAR(MAX)
SET @CauLenhTruyVan='SELECT DISTINCT HoTen'
DECLARE @MonHocThu TINYINT
SET @MonHocThu=1
WHILE @MonHocThu<=(SELECT COUNT(DISTINCT MonHoc) FROM Tbl1)
BEGIN
DECLARE @TenMonHoc NVARCHAR(MAX)
SET @TenMonHoc=(SELECT DISTINCT TOP 1 MonHoc FROM Tbl1 WHERE MonHoc NOT IN (SELECT DISTINCT TOP (@MonHocThu-1) MonHoc FROM Tbl1))
SET @CauLenhTruyVan=@CauLenhTruyVan+',(SELECT Diem FROM Tbl1 b WHERE b.HoTen=a.HoTen AND MonHoc='''+@TenMonHoc+''') AS '''+@TenMonHoc+''''
SET @MonHocThu=@MonHocThu+1
END
SET @CauLenhTruyVan=@CauLenhTruyVan+' FROM Tbl1 a'
EXECUTE (@CauLenhTruyVan)
