SQL server result in a particular pattern

Question

Currently I am getting a query result in below format,

**state** **city** 
State1 City1

State1 City2

State1 City3

State2 City4

State2 City5

But I need the result in below format,

**state** **city** 

State1 City1

       City2

       City3

State2 City4

       City5

By using only sql, how to get the result in that particular pattern for N no. of columns, so that common values of the next rows become empty.

Answer 1

You can use ROW_NUMBER in following:

select case when rn > 1 then '' else [state] end as [State], 
       city 
from(
    select  [state], 
            [city], 
            row_number() over(partition by [state] order by [state]) rn
    from YourTable
) x

OUTPUT

State   city
State1  City1
        City2
        City3
State2  City4
        City5