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 |
|
SQLnewbie2004
Starting Member
2 Posts |
 Posted - 2004-10-13 : 12:13:18
|
Hi, can anyone please help me to write a SQL query to create multiple columns for each vehicle type and year made based on each unique name and location?Here is a table to illustrate my problem:Name Location Vehicle YearJohn Toronto Honda 1990John Toronto Lexus 2000Ken Hamilton Ford 1989Christ London Benz 2001Christ London Mazda 2003Christ London Toyota 1994Tom Toronto GM 1996Tom Barrie Ford 2002I want to match the Name and the Location, and then partition the multiple vehicles and years into columns as shown below:Name Location Vehicle1 Year1 Vehicle2 Year2 Vehicle3 Year3John Toronto Honda 1990 Lexus 2000 Ken Hamilton Ford 1989 Christ London Benz 2001 Mazda 2003 Toyota 1994Tom Toronto GM 1996 Tom Barrie Ford 2002 In general, how can I partition multiple columns (ie. vehicle types and years) relating to a specified grouping of rows (ie. name and location) into one extended row? One more problem is that the number of vehicles along with their year made may vary. In my original table, I have Christ who has 3 vehicles along with 3 year made. But, say I could have Sam from Brampton who has n vehicles along with their year made. In this case I want to partition n vehicle columns and N year columns for Sam in Brampton like this:Name Location Vehicle1 Year1 Vehicle2 Year2 ... VehicleN YearNSam Brampton Toyota 1999 Honda 2000 Volks 2003 The problem is how can I determine the number of vehicles along with their year made in the list of records in order to create that many columns for each unique name and location?Any suggestion will be greatly appreciated!Sorry about the spacing problem in the tables.Thanks for the help!!  |
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2004-10-13 : 12:22:07
|
| If this is for a report - then your reporting tool can handle this quite easily.eg - In reporting Services you can use a Matrix as opposed to a table.If it has to be done in SQL - then you can find many posts on cross tabs here - Just search the the site a bit.Duane. |
 |
|
|
SQLnewbie2004
Starting Member
2 Posts |
Posted - 2004-10-13 : 12:27:23
|
| Sorry, this is strictly for the SQL Server 2000. I really need some guidelines on how to approach this problem. |
|
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
|
|
|
|
|
Please help me to write a SQL query!!!