[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剔除：利用MSSQL的NULL與字串運算特性，任何字串與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: 產生序號，第1筆RowSpan，其餘剔除

  , 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