TSQL: retrieve first column values as row data, and exclude NULLs to produce HTML table email

Using: Microsoft SQL Server 2014

I have a table that I email as a report to indicate in which States the Counts of sales (or requests for quotes, or unsubscribed emails, etc.) have changed significantly:

-- Sample Table 

    CREATE TABLE mytable(
       State       VARCHAR(2) NOT NULL PRIMARY KEY
      ,Count1      BIT 
      ,Count2      BIT 
      ,Count3      BIT 
      ,Count4      BIT 
      ,Count5      BIT 
      ,Count6      BIT 
      ,Count7      BIT 
      ,Count8      BIT  NOT NULL
    );

    INSERT INTO mytable(State,MasterCount,Count1,Count2,Count3,Count4,Count5,Count6,Count7,Count8) VALUES ('KS',NULL,1,NULL,1,1,1,1,1,1);
    INSERT INTO mytable(State,MasterCount,Count1,Count2,Count3,Count4,Count5,Count6,Count7,Count8) VALUES ('KY',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1);
    INSERT INTO mytable(State,MasterCount,Count1,Count2,Count3,Count4,Count5,Count6,Count7,Count8) VALUES ('LA',NULL,NULL,NULL,1,NULL,NULL,NULL,NULL,1);
    INSERT INTO mytable(State,MasterCount,Count1,Count2,Count3,Count4,Count5,Count6,Count7,Count8) VALUES ('MA',NULL,1,NULL,NULL,NULL,NULL,NULL,NULL,1);
    INSERT INTO mytable(State,MasterCount,Count1,Count2,Count3,Count4,Count5,Count6,Count7,Count8) VALUES ('MD',NULL,NULL,NULL,1,NULL,NULL,NULL,NULL,1);
    INSERT INTO mytable(State,MasterCount,Count1,Count2,Count3,Count4,Count5,Count6,Count7,Count8) VALUES ('ME',NULL,1,NULL,NULL,1,NULL,NULL,NULL,1)

The table looks something like this:

"State"_______"Count1"________"Count2"_____"Count3"
AK                                            1
AL 
CA              1 
CO                                1
CT                                            1
DE              1                             1
FL                                            1
GA                                            1

(for many, many counts..., for every state/federal district/protectorate/etc.)

And I email it in this way:

    DECLARE @xml NVARCHAR(MAX)
    DECLARE @body NVARCHAR(MAX)

    --Set Data
    SET @xml = CAST(( SELECT DISTINCT [State] AS 'td',''
                                     ,[Count1] AS 'td',''
                                     ,[Count2] AS 'td','' 
                                     ,[Count3] AS 'td',''
    FROM mytable
    FOR XML PATH('tr'), ELEMENTS) AS NVARCHAR(MAX))

    --Set columns

    SET @body ='
    <html><body><H3>Altered State Counts</H3>
    <table border = 1>
    <tr>
    <th>[State] </th>
    <th>[Count1] </th>
    <th>[Count2] </th> 
    <th>[Count3] </th>'

    --Set table format

    SET @body = @body+ @xml +'</table></body></html>'

    --Send Email
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SQLEMAIL',
    @body = @body,
@body_format ='HTML',
@recipients = 'me@initech.com',
@subject = 'TPS Reports' ;

This works, and produces an okay email to look at if you're using a maximized browser and are willing to scroll back and forth...what I really want is:

.................................

TPS Reports

"Count1:" AK, CA
"Count2:" CT
"Count3:" AK, AL, CA, CO, CT, DE, FL, GA

When those count columns have a 1 underneath them.

Solution

If you place this into your above code, it should provide the format you seek for your email.

--Set Data
SELECT Counts, [State] 
INTO #tempCounts
FROM (
SELECT [State]
        ,[Count1]
        ,[Count2]
        ,[Count3]
FROM mytable ) tt
    UNPIVOT 
    (States FOR Counts IN ([Count1], [Count2], [Count3])) u

SET @xml = CAST((SELECT DISTINCT Counts AS td,'', Stuff((SELECT DISTINCT ',' + [State] FROM #tempCounts t2 WHERE t2.Counts = t1.Counts FOR XML PATH('')  ), 1,1, '') AS td
FROM #tempCounts t1
FOR XML PATH('tr'), ELEMENTS) AS NVARCHAR(MAX))

--Set Columns

If you want to add more columns (especially if your columns go above 9), you'll have to work that out using dynamic SQL. Hint, you can make a list of all the columns using the FOR XML PATH just like you did for the TR elements.

Also, don't forget to drop the #tempCounts table at the end of the procedure.