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
*/
------------ |
