[SQL] Dynamic PIVOT

在 [SQL] PIVOT 和 UNPIVOT 內有人詢問，在不確定轉置選項的情況下，能否使用 PIVOT 來滿足轉置需求。

利用動態組字串方式來達到此需求。

• T-SQL 語法

CREATE TABLE #PIVOT (Employee char(8),Date datetime,Kind varchar(10),Hours numeric(4,2))
INSERT INTO #PIVOT VALUES ('AAAAA','20101001','事假',3.0)
INSERT INTO #PIVOT VALUES ('AAAAA','20101002','事假',8.0)
INSERT INTO #PIVOT VALUES ('AAAAA','20101003','事假',4.0)
INSERT INTO #PIVOT VALUES ('AAAAA','20101010','病假',8.0)
INSERT INTO #PIVOT VALUES ('AAAAA','20101020','病假',1.0)
INSERT INTO #PIVOT VALUES ('AAAAA','20101025','公假',8.0)
INSERT INTO #PIVOT VALUES ('AAAAA','20101026','公假',8.0)
INSERT INTO #PIVOT VALUES ('AAAAA','20101027','公假',8.0)
INSERT INTO #PIVOT VALUES ('AAAAA','20101027','公假',NULL)
INSERT INTO #PIVOT VALUES ('BBBBB','20101001','事假',6.0)
INSERT INTO #PIVOT VALUES ('BBBBB','20101009','病假',8.0)
INSERT INTO #PIVOT VALUES ('BBBBB','20101019','病假',8.0)
INSERT INTO #PIVOT VALUES ('BBBBB','20101019','病假',NULL)
INSERT INTO #PIVOT VALUES ('BBBBB','20101025','陪產假',8.0)
INSERT INTO #PIVOT VALUES ('BBBBB','20101026','陪產假',8.0)
INSERT INTO #PIVOT VALUES ('BBBBB','20101027','陪產假',8.0)

DECLARE 
  @pivotcol nvarchar (4000) , -- 轉置欄位變數
  @selectCol nvarchar(4000) , -- PIVOT 後要把 NULL 資料轉為 0 用
  @tsql1 nvarchar(4000) ,     -- 動態組出的 T-SQL 語法（資料 NULL 呈現）
  @tsql2 nvarchar(4000)       -- 動態組出的 T-SQL 語法（資料 0 呈現）

-- 把 Kind 資料塞進 @pivotcol 和 @selectcol 變數內
SELECT 
  @pivotcol = ISNULL(@pivotcol + ' , ' , '') + QUOTENAME(Kind) ,
  @selectcol = ISNULL(@selectcol + ' , ' , '') + 'ISNULL(' + QUOTENAME(Kind) + ',0) AS ' + QUOTENAME(Kind)
FROM #PIVOT
GROUP BY Kind

 -- 方法一：資料 NULL 呈現
SET @tsql1 = 
  '
    SELECT *
    FROM 
      (
        SELECT Employee,Kind,Hours
        FROM #PIVOT
      ) AS P
    PIVOT
      (
        SUM(Hours) FOR Kind IN (' + @pivotcol + ')
      ) AS PV
  '

-- 方法二：資料 0 呈現
SET @tsql2 = 
  '
    SELECT Employee , ' + @selectcol + '
    FROM 
      (
        SELECT Employee,Kind,Hours
        FROM #PIVOT
      ) AS P
    PIVOT
      (
        SUM(Hours) FOR Kind IN (' + @pivotcol + ')
      ) AS PV
  '
EXEC sp_executesql @tsql1
EXEC sp_executesql @tsql2

[Challenge] 找出句子中的回文字

This challenge is to find the palindromic words from sentence(s). Palindrome is a word reading the same backward as forward. While finding the palindromic words from the sentence(s), the noise words should be removed if any is present in the sentence. There is a table "Noise" that defines Noise words

For e.g. MADAM is a palindrome but MADAM! is not. So we need to first remove noise character "!" from MADAM!

There are 2 tables in challenge. One contains the data from which we need to find the Palindromes and another contains noise words.

• Sample Data

Table - Sentence

ID Sentence
-- -------------------------------
1  Hello Madam! how are you Madam?
2  She peep through the window
3  This is without any Palindrome

Table - Noise

ID Noise
-- -----
1  ?
2  !

• Expected Results

ID Sentence                  PalindromeFound PalandromicWords FoundAt
-- ------------------------------- --------------- ---------------- -------------
1  Hello Madam! how are you Madam? 2         Madam      Position : 2,6
2  She peep through the window    1         peep          Position : 2
3  This is without any Palindrome  0         NULL          NULL

• Rules
1. ID should be sorted in Ascending Order.
2. The program should run in SQL SERVER 2005 and above.
3. If no palindrome found in the sentence, then PalindromeFound column will be 0,PalandromicWords and FoundAt column will be NULL.
4. The output should be in the same way as it has been shown. Column names should be exactly the same and the result must be sorted in Ascending order of ID.

[VS] 建立時儲存新專案

對於 Visual Studio 每次建立一個專案，就必須儲存該專案的設計，實在是覺得非常麻煩。

• 工具 => 選項

• 專案和方案 => 取消［建立時儲存新專案］

• 這樣建立新專案時，就不用儲存，但離開該專案時，會詢問是否要儲存該專案。

參考資料
• Visual Studio Tricks: 建立臨時專案

[SQL] CONCAT()

CONCAT() 為 SQL Server 2012 新函數。

功能：傳回串連兩個以上之字串值的結果字串。
語法：CONCAT ( string_value1, string_value2 [, string_valueN ] )
重點：

1. 至少需要兩個輸入值
2. 所有引數會以隱含式轉換為字串
3. NULL 值會轉換為空字串
4. 所有引數皆為 NULL 時，會回傳 varchar(1) 的空字串

• 利用 AdventureWorks2012 的 [Person].[Person] 和 [HumanResources].[Employee] 來說明

SELECT  
  P.FirstName , 
  P.MiddleName ,    -- 資料內有 NULL
  P.LastName ,
  E.BirthDate ,     -- 資料型態為 date
  E.VacationHours , -- 資料型態為 smallint
  CONCAT(P.FirstName , P.MiddleName , P.LastName , E.BirthDate , E.VacationHours) AS Concat
FROM [Person].[Person] AS P
  JOIN [HumanResources].[Employee] AS E ON P.BusinessEntityID = E.BusinessEntityID

從上圖資料可以發現

1. MiddleName 資料內有 NULL，NULL 值轉為空字串。
2. BirthDate 資料型態為 date、VacationHours 資料型態為 smallint，經過隱藏式轉換變成字串。

參考資料
• 論壇問題出處
• MSDN CONCAT 
• SQL Server – String Concatenation – Faster Method

2013 母親節

太太班上小朋友的母親節卡片內容

媽媽每天的照顧

媽媽天天的付出

我卻不懂您辛苦

愛我們的心無數

您是我們的保護

有您辛苦的照顧

您是天上的禮物

真心為我們付出

好想與您永相處