Hướng Dẫn Cách Chuyển Dữ Liệu Dòng Thành Cột Trong Sql

Chạy đoạn truy vấn sau

Mã:

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

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)