[BT] Truy vấn SQL chuyển hàng thành cột

Do hôm nay rảnh rỗi, có 1 bạn chat trên Yahoo nhờ mình truy vấn 1 bài toán SQL dưới đây, thấy bài này khá hay nên mình tiện post bài lên đây hy vọng sẽ giúp ích 1 ai đó nếu gặp trường hợp tương tự.

ĐỀ BÀI: Cho 3 bảng CSDL có cấu trúc như hình bên dưới

Hãy dùng câu lệnh truy vấn trong SQL để có kết quả như sau:

LỜI GIẢI:

Sau khi xây dựng cấu trúc trên MS SQL ta có các bảng và cấu trúc dữ liệu như sau :

Đối với đề bài như trên ta có thể có 2 cách để truy vấn câu lệnh SQL

1. Với số môn fix cứng 2 cột 

Ở đây ta lấy VD 2 cột lấy ra chỉ có điểm của môn Toán và môn Văn. Vậy kết quả sau khi truy vấn sẽ là

Họ Tên HS   |  Môn học 1   |  Môn học 2   |   Học lực  |     Hạnh kiểm

STEP 1: Sử dụng từ khóa JOIN để kết nối các bản ghi với nhau từ 3 bảng CSDL và tạo ra 1 bảng tạm trong DB

select * into TestTmp from(
 select s.fullname, sub.subjectName, m.mark, s.Aptitude, s.Conduct
 from TBL_Subject sub
 join TBL_Mark m
 on sub.subjectID = m.subjectID
 join TBL_Student s
on s.studentID = m.studentID) as a 

Dữ liệu trong bảng tạm sẽ hiển thị như hình bên dưới:

STEP 2: Sử dụng từ khóa UNION ALL để kết hợp 2 bản ghi có cùng cấu trúc như sau

select fullname, sum(Toan) as Toan, sum(Van) as Van, Aptitude, Conduct
from
(
      select fullname, mark as 'Toan', 0 as 'Van', Aptitude, Conduct
      from TestTmp
      where subjectName=N'toán'
      union all
      select fullname, 0 as 'Toan', mark as 'Van', Aptitude, Conduct
      from TestTmp
      where subjectName=N'Văn'
) as a
        group by fullname, Aptitude, Conduct

Kết quả cuối cùng ==>

2.  Đối với các môn không fix cứng (Có bao nhiêu môn thì sẽ hiển thị từng đấy cột)

Câu truy vấn phải trả về kết quả như sau:

Họ tên  |  Môn học 1  |  Môn học 2  | .....| Môn học n  |  Học lực  | Hạnh kiểm

SQL query

 
----Kiểm tra nếu tồn tại bảng #TestTmp thì xóa đi và tạo lại bảng mới
IF  EXISTS (select * from dbo.sysobjects where id = object_id(N'TestTmp'))
drop table TestTmp
select * into TestTmp
from
(
    select s.fullname, sub.subjectName, m.mark, s.Aptitude, s.Conduct
    from TBL_Subject sub
    join TBL_Mark m
    on sub.subjectID=m.subjectID
    join TBL_Student s
    on s.studentID=m.studentID
) as a
GO
----- Thưc hiện truy vấn nâng cao
DECLARE @STR_UNION varchar(20)set @STR_UNION = 'union all'
DECLARE @SQLSUM nvarchar(max)
DECLARE @SQL nvarchar(max) set @SQL = ''
DECLARE @STRSBJ nvarchar(max) set @STRSBJ = ''
DECLARE @STRSBJ_TMP nvarchar(max) set @STRSBJ_TMP = ''
DECLARE @subjectName nvarchar(50)
DECLARE ListSubject CURSOR FOR select distinct subjectName from TBL_Subject
OPEN ListSubject
FETCH NEXT FROM ListSubject INTO @subjectName
WHILE @@FETCH_STATUS = 0
BEGIN
 BEGIN TRY     
    --
    set @SQLSUM = ''
    DECLARE @subjectNameChild nvarchar(50)
    DECLARE ListSubjectChild CURSOR FOR select distinct subjectName from TBL_Subject
    OPEN ListSubjectChild
   
    FETCH NEXT FROM ListSubjectChild INTO @subjectNameChild
    WHILE @@FETCH_STATUS = 0
    BEGIN
     BEGIN TRY
          if(@subjectNameChild=@subjectName)
                set @STRSBJ_TMP =  @STRSBJ_TMP + 'mark as '+@subjectNameChild+','
          else
                set @STRSBJ_TMP =  @STRSBJ_TMP + '0 as '+@subjectNameChild+','
          set @SQLSUM = @SQLSUM + 'sum('+@subjectNameChild+') as '+@subjectNameChild+','
     END TRY
     BEGIN CATCH
     END CATCH
     FETCH NEXT FROM ListSubjectChild INTO @subjectNameChild
    END
    CLOSE ListSubjectChild
    DEALLOCATE ListSubjectChild
    set @STRSBJ = @STRSBJ_TMP
    set @STRSBJ_TMP=''
    --
   
    set @SQL = @SQL + 'select fullname, '+@STRSBJ+' Aptitude, Conduct from
TestTmp
    where subjectName=N'''+@subjectName+''''
    set @SQL = @SQL + CHAR(13) + CHAR(10)
    set @SQL = @SQL + @STR_UNION
    set @SQL = @SQL + CHAR(13) + CHAR(10)
 END TRY
 BEGIN CATCH
 END CATCH
 FETCH NEXT FROM ListSubject INTO @subjectName
END
CLOSE ListSubject
DEALLOCATE ListSubject
--print @SQLSUM
set @SQL = substring(@SQL, 0, len(@SQL)-(len(@STR_UNION)+1))
set @SQL = 'select fullname, '+@SQLSUM+' Aptitude, Conduct
    from('  + CHAR(13) + CHAR(10)+ @SQL + CHAR(13) + CHAR(10) + ') as a' + CHAR(13) + CHAR(10)
    +'group by fullname, Aptitude, Conduct'
exec(@SQL)

Kết quả cuối cùng trả về

Nếu ai làm được trường hợp 2 truy vấn ngắn hơn thì post lên cho mọi người tham khảo nhé :)