-1

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
1

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
3

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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.