終於有時間上來補充動態轉換的方法了
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目錄
回首頁
