Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Sunny_175
Starting Member
2 Posts |
 Posted - 2007-05-20 : 11:52:21
|
| Dear All,I would like to ask a question about how to extract rows of records into one record with numbers of new columns.I have two tables, ([Score] and [Scoring Items]):[Score]Score ID ¡V Auto NumberStaff Name ¡V StringDate ¡V Date/TimeScoring Item ¡V NumberScore ¡V numberTime Modified ¡V Date/Time[Scoring Items]Item ID ¡V Auto NumberScoring Item ¡V StringHere are the sample data:[Score]Score ID Staff Name Date Scoring Item Score Time Modified1 Ken 19/5/2007 1 5 9/5/2007 18:39:152 Ken 19/5/2007 2 10 9/5/2007 18:39:153 Ken 19/5/2007 3 2 9/5/2007 18:39:154 Mary 20/5/2007 1 6 9/5/2007 18:41:055 Mary 20/5/2007 2 4 9/5/2007 18:41:056 Mary 20/5/2007 3 10 9/5/2007 18:41:057 Jack 16/5/2007 1 5 9/5/2007 18:43:008 Jack 16/5/2007 2 2 9/5/2007 18:43:009 Jack 16/5/2007 3 10 9/5/2007 18:43:00[Scoring Items]Item ID Scoring Item1 Customer Service2 Working Method3 PerformanceNow, I want to make a query to show the name of scoring item as a column and merge several rows into one row as follow:Staff Name Date Customer Service Working Method Performance Time ModifiedKen 19/5/2007 5 10 2 9/5/2007 18:39:15Mary 20/5/2007 6 4 10 9/5/2007 18:41:05Jack 16/5/2007 5 2 10 9/5/2007 18:43:00I don¡¦t know how to use SQL to make this output, is this possible using SQL to add columns upon records from table? And how can I write a SQL statement to make the above output?Anyone can help me? Thanks in advance. |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
Sunny_175
Starting Member
2 Posts |
Posted - 2007-05-21 : 10:35:44
|
| Thanks a lot. I'll study the article. |
 |
|
|
|
|
|
How to merge rows of records into single row