Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Comparing all columns in table Expand / Collapse
Author
Message
Posted Friday, March 22, 2013 9:46 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:30 AM
Points: 89, Visits: 368
Hello

Need help to solve one problem that I somehow caused...

I have a table that has nearly 100 columns, and i have a "copy" of this table with 2 extra columns, to maintain a kind of history. This History table it's filled with the help of a trigger in the main table, that adds a copy of the original row before the change, plus the kind of change made (delete,update), and the date of the change.

Now i'm trying to get the history for one particular ID, the PK for the main table.

How can I, create a temp table, with every row of the History table, but in each line only show differences from the previous line.

For example:
I have a table with columns from A to Z, and the original values for the rows were a to z.
The first row in the History table, has all the values, but the column A changed from a to 10.
The second row, the column T changed to 99...

The result that i'm trying to get is something like:
Row1 only the 10 in the A column, and the other columns set to null
Row2 only the 99 in the T column, and the other columns set to null
...
Post #1434341
Posted Friday, March 22, 2013 9:55 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 10:44 AM
Points: 8,585, Visits: 8,226
Not much details here to provide anything resembling a coded solution but maybe NULLIF would work?

NULLIF(HistoryTable.Col1 = MainTable.Col1)



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1434349
Posted Friday, March 22, 2013 10:05 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 11:09 AM
Points: 31,415, Visits: 13,730
Mock up something with 10 colums and provide the DDL and some sample data (with inserts) . Show a result set.

We can try to help, but as Sean mentioned, not enough info here







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1434356
Posted Friday, March 22, 2013 10:12 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:30 AM
Points: 89, Visits: 368
Thanks

@Sean Lange
I know the nullif, but to use that function i need to write every column...

@Steve Jones - SSC Editor
I'll create some sample data... The original table it's a little big :)
Post #1434372
Posted Friday, March 22, 2013 10:29 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 10:44 AM
Points: 8,585, Visits: 8,226
rootfixxxer (3/22/2013)
Thanks

@Sean Lange
I know the nullif, but to use that function i need to write every column...

@Steve Jones - SSC Editor
I'll create some sample data... The original table it's a little big :)


Honestly there really is no way around coding all the column names for something like this but that certainly doesn't mean you have to manually type them all. You can use sys.columns to help you build your sql code.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1434384
Posted Friday, March 22, 2013 10:49 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:30 AM
Points: 89, Visits: 368
GO
IF OBJECT_ID('TempDB..#OrginalTable','U') IS NOT NULL
DROP TABLE #OrginalTable

IF OBJECT_ID('TempDB..#HistoryTable','U') IS NOT NULL
DROP TABLE #HistoryTable

CREATE TABLE #OrginalTable(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
A CHAR(1),
B CHAR(1),
C CHAR(1),
D CHAR(1),
E CHAR(1)
)

CREATE TABLE #HistoryTable(
Change NVARCHAR(10),
DateValue SMALLDATETIME,
ID INT,
A CHAR(1),
B CHAR(1),
C CHAR(1),
D CHAR(1),
E CHAR(1),
PRIMARY KEY (DateValue,ID)
)

--- SOME SAMPLE DATA FINAL STATE
INSERT INTO #OrginalTable
SELECT '1','b','c','R','e' UNION ALL
SELECT '0','Y','W','Z','9'

--- SAMPLE TRIGGER DATA
INSERT INTO #HistoryTable
SELECT 'UPDATE','03/20/2013',1,'a','b','c','d','e' UNION ALL -- Row 1 - Orignal
SELECT 'UPDATE','03/21/2013',1,'1','b','c','d','e' UNION ALL -- Row 1 - After 1st change
SELECT 'UPDATE','03/20/2013',2,'X','Y','W','Z','Z' UNION ALL -- Row 2 - Orignal
SELECT 'UPDATE','03/21/2013',2,'0','Y','W','Z','Z' -- Row 2 - After 1st change

*EDITED* - Something similar to this, but only the first line complete, and the other lines, only show the values that are different from the previous line... In this sample the second row, only the "visible" the d value, in the third row the a value...

SELECT [DateValue] = GETDATE(), [Type] = 'Final',A ,B ,C ,D ,E FROM #OrginalTable WHERE ID = 1 UNION ALL
SELECT DateValue , Change , A ,B ,C ,D ,E FROM #HistoryTable WHERE ID = 1 ORDER BY DateValue DESC


Edited, and added the Select in the end of the script.

Thanks
Post #1434395
Posted Friday, March 22, 2013 11:10 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:30 AM
Points: 89, Visits: 368
@Sean League
The main problem here it's I'm not seeing how to iteract with each row to compare the values... In the Row N i need to check the values with the row N-1...

The first row, or the latest change of the history tabel compares against the current row in the orignal table, the second row in the history compares against the first row... and so on. If all comparisions were made against the current row...
Post #1434417
Posted Friday, March 22, 2013 11:27 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 8:14 AM
Points: 421, Visits: 774
Not necessarily..

current row in your audit table should match current row in your live table:
ergo. (assuming you have an incremental identity on your audit table...say you do!) the first row is discarded because there's nothing to compare again and every subsequent row compares itself to the row before it to see what value changed.

kind of like a join of audit.id = audit1.id-1 so you can compare audit.field1 equality with audit1.field1.

Make sense? If equal then null if not then audit1.field.value
Post #1434433
Posted Friday, March 22, 2013 11:46 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:30 AM
Points: 89, Visits: 368
@Erin Ramsay
If i had the Identity in the History table, that was the way to go... but i don't have, when i created this table i set the date and the original id, to be the PK.

But meanwhile i think i found a way to start working in the query, using CTE...
Something like this:

WITH CTE_History AS (
SELECT ROW_NUMBER() OVER(ORDER BY DateValue DESC) AS RowNum,*
FROM #HistoryTable
WHERE ID = 1
)

Now i just have to use the sys columns to build the query...
Post #1434449
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse