續：SQL 資料轉行

繼SQL資料轉行之後
終於有時間上來補充動態轉換的方法了

declare @Items nvarchar(max),  --不重複的Item轉成欄位
 @sql nvarchar(max)  --將要執行的SQL語法

select @Items=ISNULL(@Items,'')+'['+Item+'],' from Table1 group by Item  --取出Table內不重複的Item
set @Items=SUBSTRING(@Items,0,len(@Items)) --去除最後一個逗號

--將原本語法與轉好的欄位合併成一個字串
set @sql ='
select Name,'+@Items+'
from
(
select Name,Item,Score from Table1
) t
PIVOT (max(Score) for Item in ('+@Items+')) as a'

exec sp_executesql @sql  --執行SQL語法

經過上述方法
就可以將動態資料做轉換動作了
但是很不巧的
這幾天被SA打槍
舒服
因為串字串下SQL語法會發生 SQL Injection 危險

SQL Injection可以參考
SQL Injection (資料隱碼)– 駭客的 SQL填空遊戲(上)
SQL Injection (資料隱碼)– 駭客的 SQL填空遊戲(下)

因此又再做了修改

SQL語法：

--取出原本的資訊，依Item給ID，ID為欄位名稱
select Name,DENSE_RANK() over(order by Item)as [rid],
 Item,Score into #tab
from Table1

--利用ID做轉換的動作
select * into #myResult from (select Name,rid,Score from #tab ) t 
PIVOT (max(Score) for rid in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) as a;

--刪除轉換後整欄都為NULL的欄位
begin
 declare @i as int,@colnum as int
 set @i=0;
 set @colnum=0;
 select @colnum=MAX(rid) from #tab

 select @i=SUM(case when [1] is null then 0 else 1 end) from #myResult group by [1];
 if @i<1
 begin
  ALTER TABLE  #myResult DROP COLUMN [1] 
 end 
 else
 begin
  update #myResult
  set [1]=0
  where [1] is null
 end 
 set @i=0
 select @i=SUM(case when [2] is null then 0 else 1 end) from #myResult group by [2];
 if @i<1
 begin
  ALTER TABLE  #myResult DROP COLUMN [2] 
 end
 else
 begin
  update #myResult
  set [2]=0
  where [2] is null
 end 
 ....(將欄位延伸到10，太多就不全貼) 
end

--回傳rid對應Item資料
select rid,Item from #tab group by rid,Item order by rid

--回傳轉換後資料
select * from #myResult

drop table #tab
drop table #myResult

CS語法：

 //首先我們要先建好一個DataTable來存放轉換後資料
 DataTable dt = new DataTable();
 dt.Columns.Add("Name");  //先放Name
 while (dr.Read())
 {
  dt.Columns.Add(dr["Item"].ToString());  //將第一組回傳的Table(Item)依序放入
 }
    
 dr.NextResult();  //準備開始接收第二組回傳的Table(轉換後)
                
 while (dr.Read())
 {
  DataRow dRow = dt.NewRow();  //將目前Read的資料放到DataRow
  int CellCount = dr.FieldCount;  //取得Field數
  for (int i = 0; i < CellCount; i++)  //將欄位依序放入
  {
   dRow[i] = dr[i].ToString();
  }
  dt.Rows.Add(dRow);  //將DataRow加入DataTable
 }
 dr.Close();

以上

回sql目錄
回首頁