How to get the Distinct Record in Sql Server-CodePudding

How can I display all records in a single coulmn if the name is duplicate in sql server.

SELECT * INTO #temp
FROM (
Select 'S1' Name, '1' Age, 'A' X, 'B' Y UNION ALL
Select 'S1', '1', '', 'B'
) A

Select *
From #temp

[Output]

The expected result is:

CodePudding user response：

The rules for the expected output aren't clear. It could be the "last" row based on some order, or each column returns the maximum value in a group.

If the maximum value is needed, the following should work:

SELECT Name, Max(Age) as Age, Max(X) as X, Max(Y) as Y
FROM SomeTable
GROUP BY Name

If the "last" row is needed, there must be a way to order the results. Table rows have no implicit order. Assuming there's an incrementing ID, one could use ROW_NUMBER to find and return the latest row per group:

with rows as 
(
    SELECT ID,Name,Age,X,Y,ROW_NUMBER(PARTITION BY Name ORDER BY ID DESC) as RN
    FROM SomeTable
)
SELECT Name,Age,X,Y
FROM rows
WHERE RN=1

This will split (partition) the data by name and calculate a row_number based on descending order inside each partition. Since the rows are ordered by ID descending, the first row will the the latest one.