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
How to hide repeating cell values in query results?

Execute the following Microsoft T-SQL example script in SQL Server Management Studio Query Editor to demonstrate how to blank out repeating values in query results.

------------
-- Microsoft SQL Server T-SQL hiding repeating values in parent-child join queries
------------
-- SQL repeating values of SalesOrderID are blanked out
-- MSSQL row number over partition by - case function
USE AdventureWorks;
SELECT   SalesOrderID = CASE
                          WHEN RowNumber = 1 THEN convert(VARCHAR,a.SalesOrderID)
                          ELSE ''
                        END,
         a.SalesOrderDetailID,
         OrderQuantity = a.OrderQty,
         RowNumber
FROM     (SELECT RowNumber = row_number()
           OVER(PARTITION BY sod.SalesOrderID ORDER BY sod.SalesOrderDetailID),
                 soh.SalesOrderID,
                 SalesOrderDetailID,
                 OrderQty
          FROM   Sales.SalesOrderHeader soh
                 INNER JOIN Sales.SalesOrderDetail sod
                   ON soh.SalesOrderID = sod.SalesOrderID
          WHERE  Year(soh.OrderDate) = 2004
                 AND Month(soh.OrderDate) = 2) a
ORDER BY a.SalesOrderID,
         a.SalesOrderDetailID
GO;
/* Partial Results
 
SalesOrderID      SalesOrderDetailID      OrderQuantity     RowNumber
63119             81712                   1                 1
63120             81713                   2                 1
                  81714                   1                 2
                  81715                   2                 3
63121             81716                   2                 1
                  81717                   2                 2
63122             81718                   1                 1
                  81719                   4                 2
                  81720                   4                 3
63123             81721                   1                 1
*/
 
-- SQL select parent-child inner join query with repeating SalesOrderID value
SELECT
                 soh.SalesOrderID,
                 SalesOrderDetailID,
                 OrderQty
          FROM   Sales.SalesOrderHeader soh
                 INNER JOIN Sales.SalesOrderDetail sod
                   ON soh.SalesOrderID = sod.SalesOrderID
          WHERE  Year(soh.OrderDate) = 2004
                 AND Month(soh.OrderDate) = 2
          ORDER BY      soh.SalesOrderID,
                              sod.SalesOrderDetailID;
GO
/* Partial results
 
SalesOrderID      SalesOrderDetailID      OrderQty
63119             81712                   1
63120             81713                   2
63120             81714                   1
63120             81715                   2
63121             81716                   2
63121             81717                   2
63122             81718                   1
63122             81719                   4
*/

 

------------

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