I have:
Name PhoneNo
JOHN 555555
JOHN 555554
MIKE 111111
MIKE 122222
I want to display output like:
Name PhoneNo
JOHN 555555
555554
MIKE 111111
111112
I have:
Name PhoneNo
JOHN 555555
JOHN 555554
MIKE 111111
MIKE 122222
I want to display output like:
Name PhoneNo
JOHN 555555
555554
MIKE 111111
111112
Another possible approach is using ROW_NUMBER()
:
-- Table
CREATE TABLE #Phones (
Name varchar(10),
PhoneNo varchar(10)
)
INSERT INTO #Phones
(Name, PhoneNo)
VALUES
('JOHN', '555555'),
('JOHN', '555554'),
('JOHN', '555553'),
('MIKE', '111111'),
('MIKE', '122222')
-- Statement
SELECT
CASE
WHEN ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Name) = 1 THEN Name
ELSE ''
END AS Name,
PhoneNo
FROM #Phones
Output:
Name PhoneNo
JOHN 555555
555554
555553
MIKE 111111
122222
I want to display output like
As a general rule display matters should not be handled in the database. The DB calls should return the data and aggregates & other amalgamations/calculations based upon it, and your application's UI layer should decide what to display from that.
It is possible though, for instance using the LAG()
window function to track changes in the value:
SELECT LastName = CASE WHEN LastName = LAG(LastName) OVER (ORDER BY LastName, FirstName) THEN NULL ELSE LastName END
, FirstName
FROM Person
ORDER BY Person.LastName, Person.FirstName
It is strongly not recommended that you tightly connect the data layer and presentation layer of your application in such a manner though.