3

How can I get the output like this from the table? If I have a table for example shown below

First Name         Last Name
----------    

 1. John            Doe
 2. John           Ruggles
 3. Ricky          Rog
 4. kelly          Ali
 5. Ricky           Gyri

I want to show this as below 

First Name       Last Name

 1.John           Doe
                  Ruggles

 2. Kelly         Ali

 3. Ricky         Rog
                  Gyri

Like for each name I want to display last name. I want First Name will appear only one time. Please help me. Its a tabular data, first name and last name are different columns

Rebecca
  • 159
  • 1
  • 4
  • 13
  • Which RDBMS are you using? – Giorgos Betsos May 05 '15 at 19:13
  • I am using Microsoft MS SQL – Rebecca May 05 '15 at 19:14
  • 2
    You may have a look here http://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server or here http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server – Giorgos Betsos May 05 '15 at 19:17
  • An idea where to start looking: if you do a `select distinct(firstname)` with an `each` clause and `left outter join` each row to `select lastname ` for the given `firstname` in the `each` this would give you a firstname column with multiple lastname columns following – Seth McClaine May 05 '15 at 19:18
  • There might be a way to do it similiar to `select firstname, lastname from table group by firstname` – Seth McClaine May 05 '15 at 19:21
  • 6
    It seems like what you're trying to do might be better achieved from the UI/Display layer. You're going to have to write an interesting query to explicitly ignore only the second occurrence of a First Name. It's completely possible but probably easier to write (and possibly faster, performance-wise) from the front end. –  May 05 '15 at 19:22
  • The problem is that both the columns are in a same table, if I want to group by with first name I am not able to selecting last name:( – Rebecca May 05 '15 at 19:39
  • How to do it with front end? Kalmino – Rebecca May 05 '15 at 19:40
  • It depends on what language you're writing your front end in. The below is the general idea I would use; you'll have to find the syntax to do it in your front-end language (C#, Java, HTML, ASP.net, etc..?). Personally, I would add a "ORDER BY FirstName" clause to your SQL to group all the FirstNames and then, on the front end, store the first First Name in a variable, output it and the LastName and then loop to the next row. If it's FirstName is the same as the variable you saved, don't output it; if it's different, output it and overwrite your FirstName variable to the new name. –  May 05 '15 at 19:51
  • Also, I'm not familiar with the lag() function that Andomar supplied below but, if you wanted to keep your logic in SQL, give it a try; seems like it might be a neat tool to know about. –  May 05 '15 at 19:55
  • Thank you for your response. Actually I am using gridview control in asp.net, so making my output customize will be tricky :( – Rebecca May 05 '15 at 20:33
  • Thank you everybody for their time:) – Rebecca May 05 '15 at 21:27

1 Answers1

2

You can use the row_number() analytic function to determine if the last name has changed:

select  case
        when row_number() over (partition by FirstName 
                                order by FirstName, LastName) = 1 
            then FirstName
        else ''
        end as FirstName
,       LastName
from    YourTable
order by
        YourTable.FirstName
,       LastName

Example at SQL Fiddle.

Andomar
  • 232,371
  • 49
  • 380
  • 404