How to return empty cells for repeated values and keep only the cells which are different ?

Question

I have a query that selects some fields to display from a table

SELECT Field1, Field2, Field3, Field4 FROM table1

I want instead of returning :

To return:

How could I modify my SQL statement to return the second figure ? Or at least how to change the gridview properties of .Net to do so (if this is possible) ?

Answer 1

You can do this with a common table expression (WITH), a self-join, and the ROW_NUMBER and NULLIF functions.

WITH t AS (SELECT *, ROW_NUMBER() OVER (ORDER BY Field1) rownum FROM table1)
SELECT NULLIF(curr.Field1, prev.Field1) Field1,
       NULLIF(curr.Field2, prev.Field2) Field2,
       NULLIF(curr.Field3, prev.Field3) Field3
FROM t curr
LEFT OUTER JOIN t prev ON prev.rownum = curr.rownum - 1