Dbmail SQL Server group rows in html

Question

I can't find how to do this - help me please.

I'm using Microsoft SQL Server Management Studio

EXEC spQueryToHtmlTable 
            @html = @html OUTPUT,
            @query = N'select col1 , col2, col3 from table', 
            @orderBy = N'ORDER BY 1';

In my E-mail I will see (html table)

        col1 |    col2    | col3
row1   apple | warehouse1 | 4
row2   apple | warehouse2 | 4
row3   apple | warehouse3 | 4

How to group by col 1 and col3? (duplicate rows)

I need to get this in the email (html table):

        col1 |    col2    | col3
row1         | warehouse1 | 
row2   apple | warehouse2 | 4
row3         | warehouse3 | 

Answer 1

You can use window functions and a bit of integer arithmetic here:

SET @query = N'
select
  col1 = CASE WHEN ROW_NUMBER() OVER (PARTITION BY col1, col3 ORDER BY col2)
                 = (COUNT(*) OVER (PARTITION BY col1, col3) + 1) / 2
              THEN col1 END,
  col2,
  col3 = CASE WHEN ROW_NUMBER() OVER (PARTITION BY col1, col3 ORDER BY col2)
                 = (COUNT(*) OVER (PARTITION BY col1, col3) + 1) / 2
              THEN col3 END
from [table];
';