CODEWITHSUNDEEP
sqlsql-server
Billyuatbcs
Billyuatbcs

Reputation: 63

Display the record in column for grouping only once

I am having a large table with a basic structure like :

Aircode  FlightNumber Locator Title
A01      F01          A       Miss
A01      F01          A       Mr
A01      F01          B       Miss
A01      F01          B       Miss
A01      F02          A       Mr
A01      F02          A       Mr
A01      F02          C       Mr
A02      F01          A       Mr

I wonder if it is possible to create a query and returns the group names only once in MSSQL , such as :

Aircode  FlightNumber Locator Title
A01      F01          A       Miss
                              Mr
                      B       Miss
                              Miss
         F02          A       Mr
                              Mr
                      C       Mr
A02      F01          A       Mr

After the some search I found method using Row_Number() Over (Partition by) , but I get very confused in this case with multiple groups as I can't think of way to hide multiple values from multiple columns. I wonder if there is a proper way of proceeding this piece of data.

EDIT 1 By applied CTE and ROW_NUMBER method , it returns blank records with no values first , then Title , Locator etc. Example:

Aircode  FlightNumber Locator Title


                              Miss
                              Miss
                      A       Mr
 A01       F01        B       Miss
 A02       F02        B       Miss

Upvotes: 2

Views: 1556

Answers (1)

Malk
Malk

Reputation: 11983

Try assigning a ROW_NUMBER to each partition level and only show the text for the first one:

WITH cte AS (
  SELECT
     [Aircode]
    ,[FlightNumber]
    ,[Locator]
    ,[Title]
    ,[r1] = ROW_NUMBER() 
               OVER(PARTITION BY [Aircode] 
                    ORDER BY [AirCode])
    ,[r2] = ROW_NUMBER() 
               OVER(PARTITION BY [Aircode], [FlightNumber] 
                    ORDER BY [FlightNumber])
    ,[r3] = ROW_NUMBER() 
               OVER(PARTITION BY [Aircode], [FlightNumber], [Locator]
                    ORDER BY [Locator])
  FROM <table>
)
SELECT  [Aircode]      = CASE WHEN [r1] = 1 THEN [Aircode] ELSE '' END
       ,[FlightNumber] = CASE WHEN [r2] = 1 THEN [FlightNumber] ELSE '' END
       ,[Locator]      = CASE WHEN [r3] = 1 THEN [Locator] ELSE '' END
       ,[Title]
FROM cte
ORDER BY cte.[Aircode], cte.[FlightNumber], cte.[Locator], cte.[Title]

Upvotes: 5

Related Questions