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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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