Loading data........
Tin nhanh :
Xem Tivi Online

Tổng hợp các kênh giải trí (Đang cập nhật chức năng..)

New Update

Truyền hình SCTV

SCTV14

SCTV15

SCTV16

SCTV17

SCTV18 HD

Vietnam Channels

VTV1

VTV2

VTV3

VTV4

VTV6

VTV9

VTC Online

VTC1

VTC2

VTC3

VTC4

VTC5

VTC6

VTC Online

VTC7

VTC8

VTC9

VTC10

VTC11

VTC12

Truyền hình tp HCM

HTV1

HTV2

HTV3

HTV4

HTV7

HTV9

Radio Channels

VOV1

VOV2

VOV3

VOV4

VOV5

VOV GT-HN

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

[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é :)
Like và chia sẻ với bạn bè :

7 comments:

  1. Tôi có thể làm được ngắn hơn, liên hệ với tôi nhá.

    ReplyDelete
  2. Nhá gì mà nhá, chú cò vào đây nghịch ah :)) Ngắn hơn thì post bài ở dưới đi

    ReplyDelete
  3. vẫn còn lỗi bạn ơi? giả sử tên môn học có 2 từ trở lên thì vẫn lỗi

    ReplyDelete
  4. Các anh có thể chú thích thêm về từng đoạn code được ko ạ ?
    Và có cách nào ngắn hơn mà dễ hiểu hơn thì post lên e tham khảo với

    ReplyDelete
  5. Em thấy sử dụng câu lệnh Pivot của SQL2005 trở lên rất nhanh và gọn với thuật toán này :D

    ReplyDelete
    Replies
    1. bài toán này đc giải từ thời còn xài SQL 2000, mà bạn này bảo có cách giải ngắn hơn thì post lên cho mọi người tham khảo.

      Delete
  6. Mình dùng Toad for Oracle ko có PIVOT. có cách nào nhanh hơn hem. code này dài quá trời o.O

    ReplyDelete

Bạn có ý kiến thắc mắc hay bình luận gì về bài viết này vui lòng để lại lời nhắn.Cám ơn bạn đã ghé thăm !

 
Proudly powered by Blogger
Copyright © 2009-. The Color Of Life - All Rights Reserved