[SQL] 使用pivot進行資料轉置

之前在工作上接觸過有關問卷的案子

需要把資料轉置後輸出

以我去年紀錄的虛擬股票交易資料來做說明

如圖，2017/12/01 交易四張股票，分別為1229、4915、5234、6147

這段時間的交易想整理一下，把上方標題列改成股票代碼，左邊為交易日期，如下圖

直接從1216這隻股票代碼看下來，12/01 ~ 12/06都沒有交易

1229 12/01 ~ 12/05 各有一筆交易紀錄

用這樣的方式，更清楚地呈現每支股票的交易情況

今天介紹的，就是使用SQL的pivot來進行像這樣資料轉置

[SQL]新增資料後取得自動新增的流水號

在MS SQL Server的SP中新增資料後

KEY如果為自動新增的流水號

可用下列方式取得

以MS SQL的範例資料庫Northwind為例

產品代碼為自動新增的流水號，新增一筆產品資料後取得產品代碼

可以使用下列三種方式取得

• 取得自動新增流水號方法

1. IDENT_CURRENT(@tableName)
• 不限工作範圍，指定的資料表(@tableName)的最後一個流水號
2. SCOPE_IDENTITY()
• 目前工作階段最後一次產生的流水號
3. @@IDENTITY
• 全域變數，取得整個資料庫最後一次產生的流水號

以Products與Categories為例，在同一個工作範圍中分別新增Products與Categories資料

然後用上面三種方法分別印出來

最後一筆為78

最後一筆為8

執行SQL如下:

INSERT [Products] ([ProductName], [SupplierID], [CategoryID], [QuantityPerUnit], [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued]) 
VALUES (N'Ina', 12, 2, N'12 boxes', 13.0000, 32, 0, 15, 0)

print SCOPE_IDENTITY(); --78(目前工作階段最後一次產生的流水號)

print IDENT_CURRENT('Products'); --78(Products資料表最後一個流水號)

INSERT [dbo].[Categories] ([CategoryName], [Description]) 
VALUES (N'Ina Car', N'Seaweed and fish')

print SCOPE_IDENTITY(); --9(目前工作階段最後一次產生的流水號)

print IDENT_CURRENT('Products'); --78(Products資料表最後一個流水號)

print IDENT_CURRENT('Categories'); --9(Categories資料表最後一個流水號)

print @@IDENTITY --9(取得整個資料庫最後一次產生的流水號)

執行結果

[SP] 修改資料表描述(sp_addextendedproperty,sp_updateextendedproperty)

這篇又是瘋狂開發後，補丁的一篇文章

身為華人，只能寫英文的欄位名稱實在很難懂

尤其是像英文像我這麼爛的人

透過描述加上中文說明，比較清楚欄位用途，文件也會好寫得多

如果使用Microsoft SQL Server Management Studio 的工具來建立資料表

可以很簡單的透過UI來設定

[DB] 在MS SQL Server 2012 安裝Northwind

最近打算改寫部落格的SQL相關文章

因為資安的關係，沒辦法用公司的資料庫為範例

所以範例資料庫都是自己亂建的

毫無系統，沒有組織

所以想用微軟提供的範例資料庫Northwind來當SQL文章的範本

一來可以大家可以從微軟官網上下載安裝 下載位置

有一個共通的資料與共通的語言，溝通起來比較方便

二來我就不用自己在那邊編一些亂七八糟的資料

[DB] 附加資料庫 (Attach a database in Microsoft SQL Server 2012)

這篇文章是因為要安裝微軟的範例資料庫Northwind而生的

雖然Northwind跟Pubs因為版本問題不能用附加的方式安裝在2012

詳情請參考：[DB] 在MS SQL Server 2012 安裝Northwind

但是附加方式還是很常被拿來增加一個DB使用 (這句話再講啥?)

使用Microsoft SQL Server Management Studio的工具

附加資料庫其實還滿簡單的

[SQL] update sql from other table 的方法(update select)

餓死抬頭

update Employee 
set Name = User.Name, Dept = User.Dept 
from User
where Employee.Id = User.Id

這其實真的沒什麼技術好說的

就是一個筆記。。

當要insert的資料來源是從別的資料表來的時候

可以使用insert into select

insert into Employee (Id,Name,Dept)
select Id,Name,Dept
from User

可以一次新增多個欄位多筆資料

可是update呢？

一開始我只會這種方法

update Employee 
set Name = (select Name from User where User.Id = Employee.Id)

遇到多個欄位就挫賽了

這一次又是google救了我

參考資料：
http://www.blueshop.com.tw/board/FUM20041006152735ZFS/BRD201001071411558F1.html

[SQL] 如何執行自己組合的SQL字串(sp_executesql)

寫sp的過程中

常常會遇到必須自己組合的sql

像是一直and下去的查詢條件

或是where的值是必須經過一段複雜的運算才出來的這種

之前的作法就是很直覺的把字串組合起來

像是

set @deleteSql = 'delete from aaa where projId = @paramPrjId and place not in ('+@strPlace+') ';
exec @deleteSql

其中in 裡面的資料就是上面說的where的值是必須經過一段複雜的運算才出來的這種

結果因為一串的分號跟逗號，造成字串打架

還以遇到數字型態也會出問題

他會一直說我沒有辦法把數字跟字串組合起來

(明明c#跟java就可以...)

遇到問題當然有請google大神

我想我一天8小時的工時大概有4小時在google吧...

原來有ms sql server有一個指令叫 sp_executesql

可以在後面加上n個參數，並且定義每個參數的型態

範例如下

set @deleteSql = 'delete from aaa where projId = @paramPrjId and place not in ('+@strPlace+') ';
 EXECUTE sp_executesql @deleteSql, N'@paramPrjId int',  
                        @paramPrjId = @prjId

[SP] 在有trigger的Table下,無法執行insert select指令?

新案子我寫了很多stored produce, view, trigger

也用了很多insert into select ... 這種語法

但是只要insert到有trigger的Table

就會遇到下列錯誤訊息

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Subquery出來的資料超過一筆而造成錯誤

之前在google上找了很久

本來以為有逐筆執行的方法

一直想從AFTER下手

ALTER TRIGGER [dbo].[ASSIGEROLE]
    [dbo].[Dat_Visit2Staff]
AFTER UPDATE,INSERT
AS
BEGIN

但始終未果

於是每次要insert into select之前，我就先把trigger關掉，insert完再打開

disable trigger tr_addTel on Tel

insert into Tel (Id, Name, Tel)
select Id, Tel
from Employee

enablet rigger tr_addTel on Tel

但這樣子其實就失去trigger的意義了，

insert into select的資料沒有做到trigger裡的動作

可能會造成資料不正確

用了這個方法先暫時解決了問題，

過了幾了禮拜後其他資料表也遇到一樣的問題

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

有時候解BUG真的要天時地利人合

靈感沒來時，怎麼加班都是浪費時間

這次我又仔細看了一下錯誤訊息，

一樣是Subquery出來的資料超過一筆而造成錯誤

原來我的trigger中有這樣子的參數指定

DECLARE @staffType varchar(3)
SET @staffType = (SELECT STAFF_TYPE from INSERTED )

但是當insert into select時

SELECT STAFF_TYPE from INSERTED 就不只一筆資料

多筆資料要塞到一個 varchar 中難怪會出錯

所以需要逐筆讀取資料!!!

沒錯!!  逐筆讀取就是CURSOR

把INSERTD塞到CURSOR後逐筆讀取就搞定了

範例如下

DECLARE @staffType varchar(3)
DECLARE @staffId nvarchar(128)

DECLARE @MyCursor CURSOR 
DECLARE @SQLCommand nvarchar(200)
	
SET @MyCursor = CURSOR FAST_FORWARD 
FOR 
SELECT STAFF_ID,STAFF_TYPE from INSERTED
OPEN @MyCursor 
FETCH NEXT FROM @MyCursor 
INTO @staffId,@staffType
WHILE @@FETCH_STATUS = 0 
BEGIN
     --do something
     FETCH NEXT FROM @MyCursor 
     INTO @staffId,@staffType
END 
CLOSE @MyCursor 
DEALLOCATE @MyCursor 

看吧!!!CURSOR真好用

但眾所皆知，CURSOR可能會影響資料庫校能

有其他辦法可以不用CURSOR就能達到逐筆讀取的目的嗎?

當然有，請參考之前文章

[程式] stored procedure中不使用cursor逐步讀取資料列的方法

[DB] 新增時如何指定自動流水號的值

在最近的案子中

因為客戶的懶惰，

雖然我們做了後台輸入畫面

但是客戶還是要求我們把A資料庫的東西自動帶到B資料庫

雖然客戶懶惰，但工程式更不勤勞

怎麼可能用我寫好的輸入介面一筆一筆新增XD

是我們的系統做的不好嗎?

不對!!!!!!絕對不是這樣的!!!!!

是我知道insert select這個指令!!!

insert into B.dbo.Employee (Id, Name)
select Id, Name
from A.dbo.Employee

這樣不就方便多了

但如果PK遇到自動增加的流水號

就會遇到下列錯誤訊息

Cannot insert explicit value for identity column in table 'Employee' when IDENTITY_INSERT is set to OFF.

意思是說：不能在 IDENTITY_INSERT OFF 的情況下新增 IDENTITY的欄位

反過來說，在 IDENTITY_INSERT ON 的情況下就可以新增 IDENTITY的欄位嗎?

沒錯!!  就是這樣

因此，如果要手動新增自動識別的值，必須像下面這樣

SET IDENTITY_INSERT B.dbo.Employee ON

insert into B.dbo.Employee (Id, Name)
select Id, Name
from A.dbo.Employee

SET IDENTITY_INSERT B.dbo.Employee OFF

不過這個有個缺點，就是不能使用 insert into ... select *

必須把要新增的欄位全部寫出來，其實還蠻麻煩的

至於在甚麼情況下需要這樣呢?

為什麼不讓B的Employee資料的主鍵自動新增呢?

因為有該死的Detail檔ㄚㄚ啊!!!

自動新增的話會找不到主檔的PK

但自動新增主鍵的情況下也不是完全沒解

只是比較麻煩，要先取得新增的流水號在insert到detail

所以....嗯哼

下回預告：新增後取得自動產生的流水號

[程式] stored procedure中不使用cursor逐步讀取資料列的方法

從我第一份工作開始

公司的前輩就跟我說可以的話盡量不要用cursor

當時，我正在用strored procedure寫一份A3大小非常複雜的報表

似乎是遠傳的案子，小菜鳥第一支sp就複雜萬分

從此我對sp的印象就是：用程式很難做到的複雜事就交給stored procedure吧！！

後來證明這句話只對一半，用程式很難做到的不一定是複雜的事.....

資料庫效能也是一個大問題

沒錯！！就是效能，回到第一句話，前輩說用cursor會影響效能

可以的話盡量不要用cursor

還問前輩 "不用cursor的話為什麼要用stored procedure寫？"

可能是因為這位前輩只有大我一歲，所以才會這麼沒大沒小吧

最後在第一家公司還是沒有得到答案

後來進第二家公司之後，DBA還是說盡量不用使用cursor

雖然我忘記他講的原因是什麼了

但是他教了我一招，可以做到cursor做到的事並且不影響資料庫效能

用Temp Table取代Cursor

cursor通常用來逐筆處理資料使用，下面是一個簡單的範例（MS SQL Server 2008)

declare @myId int
declare @myName nvarchar(20)
declare @myCursor CURSOR

set @myCursor = CURSOR FAST_FORWARD
FOR
SELECT ID, NAME FROM Employee
open @myCursor
INTO @myId, @myName
WHILE @@FETCH_STATUS = 0
BEGIN

    --To Something

    FETCH NEXT FROM @myCursor
    INTO @myId, @myName

END

CLOSE @myCursor
DEALLOCATE @myCursor

用temp table來模擬cursor的操作模式，

簡單的說就是把SELECT ID, NAME FROM Employee的結果存到temp table中，在逐步讀取

所以必須先建立一個temp table如下

create table #tempEmployee
(
    ID int,
    NAME nvarchar(20)
)

然後把資料insert into select 到 #tempEmployee

insert into #tempEmployee (ID,NAME)
select ID,NAME
from Employee

接下來，就是逐步讀取這個temp table #tempEmployee

在MS SQL Server中使用SET ROWCOUNT 1來控制select時一次只撈出一筆資料

而且每做完一筆就刪掉，直到#tempEmployee沒有資料為止

declare @countTemp int --用來計算#tempEmployee還剩幾筆資料

--計算#tempEmployee資料數
select @countTemp = count(*) from #tempEmployee

while(@countTemp > 0)
begin
    set rowcount 1
    select @myId = ID, @myName = NAME from #tempEmployee
    --To Something
     
    --因為set rowcount 1的關係，所以一次只會刪一筆
    delete from #tempEmployee 

    --計算還剩幾筆，@countTemp > 0繼續
    select @countTemp = count(*) from #tempEmployee  
end

--#tempEmployee功成身退，你可以死了
drop table #tempEmployee

--把select的預設比數恢復正常
set rowcount 0

如此一般，完整的程式碼如下

declare @myId int
declare @myName nvarchar(20)

create table #tempEmployee
(
    ID int,
    NAME nvarchar(20)
)

declare @countTemp int --用來計算#tempEmployee還剩幾筆資料

--計算#tempEmployee資料數
select @countTemp = count(*) from #tempEmployee

while(@countTemp > 0)
begin
    set rowcount 1
    select @myId = ID, @myName = NAME from #tempEmployee
    --To Something
     
    --因為set rowcount 1的關係，所以一次只會刪一筆
    delete from #tempEmployee 

    --計算還剩幾筆，@countTemp > 0繼續
    select @countTemp = count(*) from #tempEmployee  
end

--#tempEmployee功成身退，你可以死了
drop table #tempEmployee