資料庫中的行列轉換

問題描述

        在評教系統中查詢教師成績處涉及到了資料的行轉列應用，首先介紹下行轉列要解決的問題：

        我們資料庫中儲存的資料結構類似為：

            

        而最後我們要達到的查詢效果為：

             

        我們將課程列分成了三列，而對應的分數列的資料作為記錄插入到了相應課程的後面。

功能來源

        為什麼會出現這樣的需求呢？

        資料庫設計的過程中為了滿足使用者的動態要求（添加欄位），可以採用定義欄位名表，定義一個欄位值的表，以達到用靜態表達動態。也就是說以資料庫表縱向的增加替代原有的橫向延伸，即記錄條數的增加替代欄位增加。

        這樣的設計帶來了靈活，但同時帶來了統計分析的麻煩，因為統計分析時有可能需要顯示欄位展開的情況。如評教系統中的具體實現：

        由於考核項目會進行動態添加刪除，因此設定的考核項目表，儲存對教師的考核項目

      

      而儲存教師評教分數的表需要擷取考核項目資料，儲存圖示為：

      

      最終需要的顯示效果是要將考核項目列為標題，而分值作為記錄添加到對應的考核項目下。

      

       此處我們我們通過將動態變化的考核項目作為新的表的記錄來儲存帶來了設計的靈活性，而顯示的時候卻要進行行列的轉換才能得到想要的結果。

具體實現

       尋找相關資料進行實現，明白了行轉列又分成了靜態和動態之分，靜態是不需要擴充的，很容易實現，而針對評教系統中的功能則是對應的動態實現。

       針對第一個執行個體

               1.通過執行字串的拼接實現動態行轉列

      

--變數按sql語言順序賦值declare @sql varchar(500)set @sql = 'select 姓名'select @sql = @sql+',max(case 課程 when '''+ 課程 +''' then 分數 else 0 end)['+課程+']'from(select distinct 課程 from tb)a--同from tb group by課程，預設按課程名排序set @sql= @sql + ' from tb group by 姓名'exec(@sql)

              2.使用isnull、pivot函數

declare @sql varchar(8000)--獲得課程集合select @sql=isnull(@sql+',','')+ 課程 from tb group by 課程           set @sql='select * from tb pivot (max(分數) for 課程 in ('+@sql+')) a'exec(@sql)

             3.添加算總分、平均分的要求

declare @sql varchar(8000)select @sql=isnull(@sql+',','')+ 課程 from tb group by 課程set @sql='select m.* , n.總分,n.平均分 from(select * from (select * from tb) a pivot (max(分數) for 課程 in ('+ @sql+')) b) m ,(select 姓名,sum(分數)總分, cast(avg(分數*1.0) as decimal(18,2)) 平均分 from tb group by 姓名) nwhere m.姓名= n.姓名'exec(@sql)

新得小結

          雖然評教系統中已經實現了對行列資料的轉換，但想要使用另外一種方法進行實現。進行了對行轉列功能的搜尋實現之後，在轉接到評教系統上的應用上來還是碰到了很多問題，到現在仍沒有完全解決，只得暫時先放一放了。對資料庫的靈活設計也有了一定的體會，在考慮靈活性的同時也要考慮使用者的體驗度的。對行列互相轉換的還是需要更多的思考呀。

        相關連結：http://www.2cto.com/database/201203/122708.html

                           http://www.cnblogs.com/zhangzt/archive/2010/07/29/1787825.html