DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS

 

Hiding Duplicate Labels in Muli-Level Lists

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:

Color ProductName
Black Chainring
Full-Finger Gloves, L
Full-Finger Gloves, M
Full-Finger Gloves, S
Half-Finger Gloves, L
Half-Finger Gloves, M
Half-Finger Gloves, S
HL Crankarm
HL Crankset
HL Mountain Frame - Black, 38
HL Mountain Frame - Black, 42
HL Mountain Frame - Black, 44
HL Mountain Frame - Black, 46
HL Mountain Frame - Black, 48
HL Mountain Front Wheel
HL Mountain Rear Wheel
HL Road Frame - Black, 44
HL Road Frame - Black, 48
HL Road Frame - Black, 52
HL Road Frame - Black, 58
HL Road Frame - Black, 62
HL Road Front Wheel
HL Road Rear Wheel
LL Crankarm
Women's Tights, L
Women's Tights, M
Women's Tights, S
Blue Classic Vest, L
Classic Vest, M
Classic Vest, S
HL Touring Frame - Blue, 46
HL Touring Frame - Blue, 50
HL Touring Frame - Blue, 54
HL Touring Frame - Blue, 60

Related article:

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

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE