Pivot and email in SQL

Hi,

I have the following query

SELECT * FROM 
(
	SELECT UserName, Activity FROM Contact

) src
PIVOT
(
	COUNT(Activity)  for Activity in ([Call],[Contact],[Deadline])
) piv

ORDER BY UserName

Open in new window


It gives me the result like the following

Username Call Contact Deadline
user1     10    5      2
user2     2     2      2
user3     8     12     4
user4     5     2      6
user5     7     8      2


I need to use it to send out email, is there a way to do this? thanks (the email part is working fine, but I need help to put it in HTML format)

 SELECT         @MAIL_BODY1 = @MAIL_BODY1 + 
			'<tr>' +
			'<td>' + UserName + '</td>' +
			'<td>' + Something + '</td>' +
			'<td>' + Something  + '</td>' +
			'</tr>'
FROM            Something

Open in new window

mcrmgAsked:
Who is Participating?
 
ste5anSenior DeveloperCommented:
E.g.

DECLARE @Sample TABLE
    (
        Username NVARCHAR(255) ,
        Call INT ,
        Contact INT ,
        Deadline INT
    );

INSERT INTO @Sample ( Username ,
                      Call ,
                      Contact ,
                      Deadline )
VALUES ( 'user1', 10, 5, 2 ) ,
       ( 'user2', 2, 2, 2 ) ,
       ( 'user3', 8, 12, 4 ) ,
       ( 'user4', 5, 2, 6 ) ,
       ( 'user5', 7, 8, 2 );

DECLARE @Body NVARCHAR(MAX) = '';
SELECT @Body += (   SELECT 'blue-1' AS [thead/tr/@class] ,
                           '4' AS [thead/tr/td/@colspan] ,
                           'DISPLAY' AS [thead/tr/td/b] ,
                           'header_gtgt' AS [thead/tr/td/span/@class] ,
                           '  » ' AS [thead/tr/td/span/b] ,
                           'Summary' AS [thead/tr/td] ,
(   SELECT (   SELECT   'blue-4' AS [@class] ,
                        ISNULL(S.Username, '') AS td ,
                        NULL ,
                        ISNULL(S.Call, ' ') AS td ,
                        NULL ,
                        ISNULL(S.Contact, ' ') AS td ,
                        NULL ,
                        ISNULL(S.Deadline, ' ') AS td
               FROM     @Sample S
               ORDER BY S.Username
               FOR XML PATH('tr'), TYPE )
    FOR XML PATH('tbody'), TYPE )
                    FOR XML PATH(''), ROOT('table'));
SELECT @Body;

Open in new window

0
 
mcrmgAuthor Commented:
Thanks for the quick reply. This is actually the original question, I got help from EE to display the counts. But I need to display it horizontally. I use pivot to do it. But I do not know how to put it in html.

thanks
0
 
ste5anSenior DeveloperCommented:
Did you run my sample? Did you put the HTML into a file to test it?

It exactly does what you want. It outputs your pivoted data. Obviously - as you have missed to post a concise and complete example, including table DDL with sample data INSERT statements as one runnable T-SQL script - I had to mock up yours. And why should I waste time doing so with the base table(s) and the pivoting, which you already have? Thus @Sample is your pivoted result.
0
 
mcrmgAuthor Commented:
I see the way you did it now. Sorry about it. I missed the way your use @ in creating the table.  thank you very much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.