September 9, 2009 at 9:00 am
Hello,
I am trying to come up with a way that I can take 2 or more tables and generate an output using the layout below:
| fieldName | OldValue | NewValue | ModDate |
-----------+ ----------+----------+----------
Field1 'yes' 'No' 8/23/2009
Field2 15 0 8/23/2009
Field1 'No' 'Maybe' 8/31/2009
...
... etc
Based on table(s)
Table_1
(
SomeKey int,
Field1 varchar(255),
Field2 int,
Field3 varchar(25)
)
Im trying to create this output as an easy way to view our audit data. There is no aggragation needed... just report the data in the layout shown above... however Im not all that well versed in SQL and I am having trouble using the above as a simple test... I have about 2 dozen audit tables Im going to have to report on with a max total of 40 columns.... So I am quite befuddled on how to go about this...
Anyone have any suggestions as to how I might go about this?
Thanks
Kevin
September 9, 2009 at 11:32 pm
Please see the link in my signature about how to post sample data.
I'm making many assumptions here, but you could possibly do something along the lines of
SELECT 'Table1' TableName, Field1, Field2, Field3
FROM TABLE1
UNION ALL
SELECT 'Table2', Field1, Field2, Field3
FROM TABLE2
UNION ALL
SELECT 'Table3', Field1, Field2, Field3
FROM TABLE3
UNION ALL
SELECT 'Table4', Field1, Field2, Field3
FROM TABLE4
It's very possible that I'm completely misunderstanding your question, but following the advice in the article I mentioned will help clear that up.
September 10, 2009 at 7:08 am
Thanks for the reply and yes you misunderstood... i did as you advised and went the link. Here is the script to illustrate my question and problem:
--// create temp table of expected output
IF OBJECT_ID('TempDB..#tblOutput','U') IS NOT NULL
DROP TABLE #tblOutput
Create Table #tblOutput
(
EventIDint,
Barcodeint,
FieldNamevarchar(255),
OldValuevarchar(2000),
NewValuevarchar(2000),
ModUservarchar(25),
ModDateDateTime
)
--// insert data into expected output
Insert into #tblOutput
(EventID,Barcode,FieldName,OldValue,NewValue,ModUser,ModDate)
Select 100201,431234,'Attending','','YES','jjdoe','08/23/2009 12:31:23' UNION ALL
Select 100201,431234,'Attending','YES','NO','jjdoe','08/23/2009 14:31:23' UNION ALL
Select 100201,431234,'Driving','','YES','jjdoe','08/23/2009 12:31:23' UNION ALL
Select 100201,431234,'Boating','','NO','jjdoe','08/23/2009 12:31:23' UNION ALL
Select 100201,431234,'Dinner','','YES','jjdoe','08/25/2009 12:31:23' UNION ALL
Select 100201,431234,'Dinner','YES','NO','jjdoe','08/25/2009 12:54:13'
--// create temp table of the source data that the output will pull from
IF OBJECT_ID('TempDB..#tblSource','U') IS NOT NULL
DROP TABLE #tblSource
Create Table #tblSource
(
source_idint identity,
Barcodeint,
ModUservarchar(25),
ModDate datetime,
ModAction varchar(25),
Attending varchar(5),
Driving varchar(5),
Boating varchar(5),
Dinner varchar(5)
)
--// insert some source data
Insert into #tblSource
(Barcode,ModUser,ModDate,ModAction,Attending,Driving,Boating,Dinner)
Select 431234,'jjdoe','08/23/2009 12:31:23','UPDATE Orig value','','','','' UNION ALL
Select 431234,'jjdoe','08/23/2009 12:31:23','UPDATE NEW value','YES','YES','NO','' UNION ALL
Select 431234,'jjdoe','08/23/2009 12:31:23','UPDATE Orig value','YES','YES','NO','' UNION ALL
Select 431234,'jjdoe','08/23/2009 12:31:23','UPDATE NEW value','NO','YES','NO','' UNION ALL
Select 431234,'jjdoe','08/25/2009 12:31:23','UPDATE Orig value','YES','YES','NO','' UNION ALL
Select 431234,'jjdoe','08/25/2009 12:31:23','UPDATE NEW value','YES','YES','NO','YES' UNION ALL
Select 431234,'jjdoe','08/25/2009 12:31:23','UPDATE Orig value','YES','YES','NO','YES' UNION ALL
Select 431234,'jjdoe','08/25/2009 12:31:23','UPDATE NEW value','YES','YES','NO','NO'
--// display the source data
Select * from #tblSource
--// display expected output
Select * from #tblOutput
The source table shows the data with the column names horizontal... the output shows the same columns vertical.... Data in the update orig column goes under the OldValue column etc...
My problem is I cant figure out how to go from the source structure to the output layout.... the source is an example of my audit table and of course the output is the layout the users want to see....
hope this helps
September 10, 2009 at 8:21 am
Much better, thank you.
Now, before I make this thing needlessly complex, is there anything that can link an Orig value to a New Value beyond the fact that they're sequential? In your source data, it appears that the Orig is immediately followed by a New, but there's no shared value that is unique to the pair. Is that just an oversight in your sample data, or is there really no field that establishes that relationship in your audit table?
[Edit] Also, how many rows of data are we talking about? This is a pretty prime example of why you never want to store data in a pivoted format, but it does give us a fun query to figure out. Whether or not said query will perform well is another story entirely.
September 10, 2009 at 8:33 am
Hi Seth,
Currently there is no way to link the 2(orig and new) rows to a single transaction. However in the ongoing days I will be adding a transaction key that will identify the 2 rows to a single transaction... I apologize for not putting that into my example... So it was an oversight on my with the example.
The number of rows could be quite large for any given table. The output table is just that ... output in a pivot format... ultimately the result set from the output table will be dropped into an excel worksheet for the analysts to use. The total number of tables that will feed the output are about 23 tables.
thanks 🙂
September 10, 2009 at 8:58 am
I added an integer field named PairID to your data, each pair increments the int by 1. Here is some code to generate that result set. This wasn't too complicated, but if I'm understanding your requirements right, it will become so once you replace the hard coded field lists with some dynamic SQL and extend this to many different tables.
This could be taken and
A: Converted to dynamic SQL, pulling the field lists out of the system tables for each table.
B: Cursor added to repeat the dynamic SQL for each table you need to do this to.
C: Another piece added to Union all of the data together and extract your final result set.
;WITH UnPivotCTE AS (
SELECT Old.PairID, Old.FieldName, Old.OldValue, New.NewValue
FROM
(
--Unpivot the table.
SELECT PairID, FieldName, Oldvalue
FROM
(SELECT PairID, Attending, Driving, Boating, Dinner
FROM tblSource
WHERE ModAction = 'UPDATE Orig value' ) AS p
UNPIVOT
(OldValue FOR FieldName IN
(Attending, Driving, Boating, Dinner)
)AS unpvt
) Old
INNER JOIN
(
--Unpivot the table.
SELECT PairID, FieldName, Newvalue
FROM
(SELECT PairID, Attending, Driving, Boating, Dinner
FROM tblSource
WHERE ModAction = 'UPDATE NEW value' ) AS p
UNPIVOT
(NewValue FOR FieldName IN
(Attending, Driving, Boating, Dinner)
)AS unpvt
) New ON Old.PairID = New.PairID AND Old.FieldName = New.FieldName
WHERE Old.OldValue New.NewValue
)
SELECT '100201', S.Barcode, U.FieldName, U.OldValue, U.NewValue, S.ModUser, S.ModDate
FROM (SELECT PairID, BarCode, ModUser, ModDate
FROM tblSource
WHERE ModAction = 'UPDATE NEW value') S --Pull the shared fields from the new Line)
INNER JOIN UnPivotCTE U ON S.PairID = U.PairID
ORDER BY ModDate, FieldName
[Edit] Oops, didn't post all the code.
September 10, 2009 at 9:04 am
looks like some code is missing....?
lol im going to have to study this a bit....
September 10, 2009 at 9:09 am
September 10, 2009 at 9:13 am
The good thing about the above example is most of the pieces are derived tables that can be run individually so you can see what they're doing.
This relies completely on the unpivot operator introduced in 2005. I'm basically unpivoting the old data, unpivoting the new data, joining them on their pairID and only keeping the ones with different oldvalue/newvalue pairs.
The main reason it looks so confusing is that unpivot has really funky syntax. The concept is actually pretty simple. This looked way worse when I had it all as derived tables (I'm still a 2000 guy at heart), but I cleaned it up a bit with 1 CTE. If you're not familiar with CTE's, they're basically just derived tables that are pulled out that can then be referred to.
September 10, 2009 at 9:15 am
heh I think I was refereing to the
;WITH UnPivotCTE AS (
at the beginning of the code piece.... as I said I am going to need to study this a bit... this kind of particular coding is new to me.
Also I really appreciate your help!!
September 10, 2009 at 9:19 am
My pleasure. Take some time to get familiar with that piece and then post back once you start broadening it to meet your overall goal with any questions or with the final solution if you knock the rest out on your own.
September 10, 2009 at 9:21 am
Garadin (9/10/2009)
My pleasure. Take some time to get familiar with that piece and then post back once you start broadening it to meet your overall goal with any questions or with the final solution if you knock the rest out on your own.
I will do that... I dont think I will be the only one who runs across things like this. So I will be happy to post back my finished product.
Thanks again!!:-D
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply