2018年2月12日 星期一

[2005] 以SQL直接產生HTML Table-Rowspan作法

先前[2005] SQL直接產生HTML Table並寄送範例已概略說明寄送HTML EMAIL作法,本範例將說明RowSan及換行(br)兩項重點,也建議請手動製作出HTML TABLE,才較容易理解,資料如下圖所示。
Emaial通知內容如下:
1.  RowSan
在產生RowSpan資料的第一列(Row)屬性中需載明Rowspan列數 (:Rowspan=3),其餘各列中的同位置TD需剔除,詳細請參考HTML語法。
ü   Rowspan列數:以SUM(1) OVER()計算產生。
ü   TD剔除:利用MSSQLNULL與字串運算特性,任何字串與NULL值運算字串連結果為NULL,可參考NULL處理應注意事項及常用函數文章所述。

2.  換行(br)
產生HTML Email利用XML方式自動產生,因此在運算中加入任何HTML原生Tag,測試結果均發現無法得到預期結果,需以自訂特殊符號克服。


DECLARE @HTML      nvarchar(MAX) ;
--DROP TABLE #Notify   

SELECT DENSE_RANK() OVER(ORDER BY TYPE) NO
  , SUM(1) OVER(PARTITION BY TYPE)   L1_ROWS                  --重點1: 計算出Row Span
  , DENSE_RANK() OVER(PARTITION BY TYPE ORDER BY PROD) L1_SEQ --重點1: 產生序號,第1RowSpan,其餘剔除
  , TYPE
  , PROD
  , AMT
  , REPLACE(Store, ',', '[~]') Store --將逗號(,)置換成自訂特殊符號([~]),確保正確取代
    INTO #Notify
FROM (VALUES('DDR2', 'RAM' , 40,  '光華店,站前站')
            , ('DDR3', 'RAM' , 160, '站前站')
            , ('DDR4', 'RAM' , 300, '站前站')
            , ('22"',  'LCD' , 25,  '光華店,公館站')
            , ('24"',  'LCD' , 75,  '公館站')
        ) Sales(PROD, TYPE, AMT, Store)


SET @HTML =
 N'<table border="1" style="font-family:Consolas;border-collapse:collapse" width="400">' +
 N'<tr style="color:White;background-color:#4d4e4f; font-weight:bold;" align=center>'+
 N'<td>No</td><td>Type</td><td>#</td><td>Prod</td><td>Amt</td><td>Store</td></td>'

SET @HTML = @HTML +
   COALESCE(CAST ( (
           SELECT
                --1. No
                      CASE WHEN L1_SEQ = 1 THEN '' END + 'center' [td/@align]
                  , CASE WHEN L1_SEQ = 1 THEN L1_ROWS END  [td/@Rowspan] --重點1: RowSpan
                  , CASE WHEN L1_SEQ = 1 THEN CAST(NO AS VARCHAR(10)) END td, ''  
                  ------------------     
                  --2. TYPE
                  , CASE WHEN L1_SEQ = 1 THEN '' END +  'left' [td/@align]
                  , CASE WHEN L1_SEQ = 1 THEN L1_ROWS END [td/@Rowspan]
                  , CASE WHEN L1_SEQ = 1 THEN TYPE END  td, ''
                  ---------------------- 
                  --3. L1_SEQ                                                                    
                  , 'center' [td/@align]
                  , CAST(L1_SEQ AS VARCHAR(20))  td, ''
                ----------------------
                --4. PROD                   
                  , 'left' [td/@align]
                  , PROD  td, ''
                  ------------------
                --5. AMT                    
                  , 'right' [td/@align]
                  , CASE WHEN AMT<50 THEN '#f8b0a0' ELSE '' END  [td/@bgcolor]
                  , CAST(AMT AS VARCHAR(20))  td, ''
                  --------------
                --6. Store                  
                  , 'LEFT' [td/@align]
                  , Store  td, ''     --重點2: 在產生HTML語法中,不可自行增加HTML語法,如
           FROM #Notify
         FOR XML PATH('tr'), TYPE
   ) AS NVARCHAR(MAX) ) ,'') +
  N'</table>'

                                      
PRINT @HTML   
SET @HTML = REPLACE(@HTML,'[~]', '<br>') --重點2:將自訂特殊符號以<br>取代

EXEC msdb.dbo.sp_send_dbmail
          @recipients      = 'HsuHarry@gmail.com'
         , @subject        = 'Sales Report'
         , @body           = @HTML

沒有留言:

張貼留言