By Kalman Toth, M.Phil., M.Phil., MCDBA, MCITP

June 4 , 2008

When creating a multi-level GROUP BY summary (rollup) or list for a report with sorting, it is undesirable for the same entry to repeat at higher levels. In the following demo, the higher level category dimension “color” would repeat for all the products of the same color. It is a common practice in business reporting to hide the dupicate label from the second entry on. In reporting though, when the list continues after pagebreak the label is printed again once at page header for better readability.

We use a self-join of nested Common Table Expression (CTEs) current row with next row and the "Swiss Army Knife" CASE function to decide when to hide the label. That works well for all except for the last or first depending the way we define the self-join between adjacent rows.  To take care of this issue, we use a UNION of first row with labels with the rest of result set with label hiding. The row_count() function plays a pivotal row in the solution since we use the sequence numbering to do the self join.  Nested CTE-s are applied to such an extent that the final SELECT query becomes trivial.

First we create a small data set for the demo:

USE tempdb;

-- SQL select into create table

SELECT ProductID,

       Color,

       ProductName = Name

INTO   ReportByColor

FROM   AdventureWorks2008.Production.Product

WHERE  Color IS NOT NULL;

-- SQL Common Table Expression

WITH cteProduct

     AS (SELECT SEQUENCE = row_number()

                             OVER(ORDER BY Color, ProductName),

                Color,

                ProductName

         FROM   ReportByColor r),

     cteTabular

     AS (SELECT a.SEQUENCE,

                a.Color,

                a.ProductName

         FROM   cteProduct a

         WHERE  SEQUENCE = 1

         UNION

         SELECT b.SEQUENCE,

                Color = CASE

                          WHEN a.Color = b.Color THEN ''

                          ELSE b.Color

                        END,

                b.ProductName

         FROM   cteProduct a

                JOIN cteProduct b

                  ON a.SEQUENCE = b.SEQUENCE - 1

         WHERE  b.SEQUENCE > 1)

SELECT   Color,

         ProductName

FROM     cteTabular

ORDER BY SEQUENCE

GO

This is the partial result set:

Related article:

How to write a SQL statement which can hide Duplicate values during query output