当前位置: 动力学知识库 > 问答 > 编程问答 >

sql server - 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!

网友答案:

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
分享给朋友:
您可能感兴趣的文章:
随机阅读: