CODEWITHSUNDEEP
sqlsql-serverreportms-access-2007
javier
javier

Reputation: 3

Sql: display just the changed data in each row

I would like to know how can I have results like below in Access (sql or reports) or with SQL SERVER?

--------------------------------------------------------------
| ID    |  Name  |  Job        |  Date        |  Hobby       |
--------------------------------------------------------------
| 1     |  Alex  |  carpenter  |  12/02/2006  |  basketball  |
| 1     |        |             |              |  baseball    |
| 2     |  Lara  |  dancer     |  06/07/2015  |  running     |
| 2     |        |             |              |  cooking     |
| 2     |        |             |              |  swimming    |
--------------------------------------------------------------

The results that i have after executing my query are for example:

--------------------------------------------------------------
| ID    |  Name  |  Job        |  Date        |  Hobby       |
--------------------------------------------------------------
| 1     |  Alex  |  carpenter  |  12/02/2006  |  basketball  |
| 1     |  Alex  |  carpenter  |  12/02/2006  |  baseball    |
| 2     |  Lara  |  dancer     |  06/07/2015  |  running     |
| 2     |  Lara  |  dancer     |  06/07/2015  |  cooking     |
| 2     |  Lara  |  dancer     |  06/07/2015  |  swimming    |
--------------------------------------------------------------

The idea here is to always keep the first row as it is, but the following ones with the same ID should contain just the ID and the data that change, as shown in the example above.

The query here is not so important because I just want to have a particular display of the results.

Thanks a lot!

Upvotes: 0

Views: 85

Answers (1)

A.J
A.J

Reputation: 382

From your question, it appears you are trying to remove the duplicate rows for the 'Name', 'Job', and 'Date' fields and have them displayed only for the first row in each combination.

To accomplish this, you can try the below:

  1. Have your data in a temporary table
  2. Apply the partition by clause for finding the Rank for each combination (of name, job, date fields) and add that as an additional column in the temporary table
  3. update the temporary table to blank out the name, job, date fields where the rank value is > 1, and select the columns you need. This should make your table look like the one you had mentioned

Upvotes: 1

Related Questions