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 |
|
vsempoux09
Starting Member
3 Posts |
 Posted - 2009-06-23 : 03:22:21
|
| Hello all,I'm having a little trouble with (a new function for me) the pivot statement.Let me give you a quick overview of my problem.I have two tables (order and tracking).In my order table i have various columns:UID (unique id)owner (responsable for this order)creationDatestatuscompany...There is a customizable tracking system which will record allchanges on predefined fields and logs that in the tracking table.The tracking table has the following columnsUIDrecordID (uid of the order record)changedate (date when the change occurs)changefields (field on which the tracking is triggered, in this case status )oldValue (value of the status before the change)newvalue (value of the status after the change)user (user who did the change)My problem is now that i want to list (in one line) all the changes that have already occured until todayi have been strugling quite some time with this, but i'm stuck.Could anyone give me some pointers, tips, .. to help me out.thanks in advance.Greetings,Vincent |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-23 : 03:31:47
|
Do you also have an example of the preferred result?
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
vsempoux09
Starting Member
3 Posts |
Posted - 2009-06-23 : 03:50:51
|
| Hi,Thanks for the quick reply.The output should look something like this :order.uid, order.ref, order.owner, order.status, order.company,tracker.changedate, tracker.user, tracker.oldvalue, tracker.newvalue, tracker.changedate, tracker.user, tracker.oldvalue,tracker.newvalue, ... (and this as many times repeated that there are records in the tracker for the unique order id).Is this possible ?Greetings,Vincent |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-23 : 03:55:31
|
Yes. Use INNER JOIN to tie the two tables together.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
vsempoux09
Starting Member
3 Posts |
Posted - 2009-06-23 : 04:38:08
|
| Hi,The inner join gives me a multiple line result (on line per entry in the tracker).The result i'm looking for should be in one line.Greetings,Vincent |
|
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
|
|
|
|
|
|
|
Little help with pivot function