Forum Home
Press F1
 
Thread ID: 127096 2012-10-03 20:50:00 SQL pivot, filter, and most recent Mike (15) Press F1
Post ID Timestamp Content User
1304977 2012-10-03 20:50:00 Hi all,

I have a SQL database with a lot of tables with data being regularly modified. Some of the data needs to be transferred to another database on another server when it is updated.

I have an audit table that records every record when it is edited, however it records them in a field per row format, so for every field in an edited record there's a new row in the audit table to record the value of the field from that edited record. It records every field whether it has been edited or not.

I would like to have a view (or something) that pivots those fields into a single row for each edited record, filters to only have the data I need (so ignoring edited records in tables I don't care about), and the only display the most recent edit on any given record (so if its edited twice, I only need the most recent to update my second database).

Any ideas how to do any/all of the above? :)

Example of Audit Table:


AUDITID
RECORDID
EDITTABLE
EDITDATE
EDITUSER
EDITFIELD
EDITVALUE


1
1
Computers
2012-09-29 10:10:14.547
Mike
Brand
Acer


2
1
Computers
2012-09-29 10:10:14.547
Mike
RAM
2


3
1
Computers
2012-09-29 10:10:14.547
Mike
Colour
Red


4
2
Chairs
2012-09-29 10:23:52.132
Bob
Brand
Comfy


5
2
Chairs
2012-09-29 10:23:52.132
Bob
Colour
Blue


6
2
Chairs
2012-09-29 10:23:52.132
Bob
Wheels
5


7
2
Chairs
2012-09-29 10:23:52.132
Bob
Arms
No


8
3
Bottles
2012-09-29 11:19:23.424
Mike
Capacity
300


9
3
Bottles
2012-09-29 11:19:23.424
Mike
Lid
Yes


10
1
Computers
2012-09-30 14:32:26.223
Mike
Brand
HP


11
1
Computers
2012-09-30 14:32:26.223
Mike
RAM
4


12
1
Computers
2012-09-30 14:32:26.223
Mike
Colour
Red


13
4
Chairs
2012-10-01 08:47:39.353
Sarah
Brand
NotSoComfy


14
4
Chairs
2012-10-01 08:47:39.353
Sarah
Colour
Blue


15
4
Chairs
2012-10-01 08:47:39.353
Sarah
Wheels
3


16
4
Chairs
2012-10-01 08:47:39.353
Sarah
Arms
Yes




So above record 1 is edited twice, so I'd only need the most recent edits for it. The values in EDITFIELD would need to be columns, and values in EDITVALUE needs to be the values in those columns.


Any ideas?

Cheers,
Mike. 		Mike (15)
1304978 2012-10-03 21:12:00 Which database engine are you using? The syntax for pivots of any kind tend to be very engine-specific, and many databases can't actually do a pivot at all - in those cases, the result can usually be achieved using dynamic SQL. Erayd (23)
1304979 2012-10-03 21:19:00 Which database engine are you using?Microsoft SQL Server 2008 R2 for database one, MS SQL Server 2008 for database two.

Cheers,
Mike. 		Mike (15)
1304980 2012-10-03 22:08:00 MS-SQL can pivot natively - documentation for that feature is here (msdn.microsoft.com(v=sql.105).aspx). Erayd (23)
1304981 2012-10-04 20:00:00 MS-SQL can pivot natively - documentation for that feature is here (msdn.microsoft.com(v=sql.105).aspx).Thanks I'll have a play with that. Will that also allow me to filter to only display certain tables (see EDITTABLE in my example) and also only the most recent for each record?

Cheers,
Mike. 		Mike (15)
1304982 2012-10-04 20:33:00 Thanks I'll have a play with that. Will that also allow me to filter to only display certain tables (see EDITTABLE in my example) and also only the most recent for each record?That's what WHERE and HAVING are for :). Erayd (23)
1304983 2012-10-05 00:00:00 I suggest you're looking for a "TOP 1 INNER SELECT" also known as "a select within a select". Not to be confused with a "INNER JOIN"

This is not really a question for this forum, as it's an advanced feature of SQL server.

I suggest using the SQLCentral.com forum. 		kingdragonfly (309)
1