December 12, 2012 at 5:35 am
Dear all ,
I just need to select only the columns that don't match the parent row
eg
row1
id col1 col2 col3 col4 col5
1 This That or yes me
row 2
id col1 col2 col3 col4 col5
1 this that not yes you
in this situation i need any t-sql that would give me
result:
col3 col5
not you
thanx in advance 🙂
December 12, 2012 at 7:33 am
Hi I see that you are pretty new around here. From what you posted there is no chance anybody can offer much help. Take a look at the first link in my signature for best practices when posting questions.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 12, 2012 at 7:53 am
I'll assume you have a case-insensitive collation for your instance of SQL Server. How about something like this:
CREATE TABLE #TABLE1 (
ID INT NOT NULL PRIMARY KEY CLUSTERED,
COL1 varchar(6),
COL2 varchar(6),
COL3 varchar(6),
COL4 varchar(6),
COL5 varchar(6)
)
CREATE TABLE #TABLE2 (
ID INT NOT NULL PRIMARY KEY CLUSTERED,
COL1 varchar(6),
COL2 varchar(6),
COL3 varchar(6),
COL4 varchar(6),
COL5 varchar(6)
)
INSERT INTO #TABLE1 VALUES (1, 'This', 'That', 'or', 'yes', 'me')
INSERT INTO #TABLE2 VALUES (1, 'This', 'That', 'not', 'yes', 'you')
;WITH T1_ROWS AS (
SELECT ID, FIELD, COLVALUE
FROM #TABLE1
UNPIVOT (COLVALUE FOR FIELD IN (COL1, COL2, COL3, COL4, COL5)) AS UPVT
),
T2_ROWS AS (
SELECT ID, FIELD, COLVALUE
FROM #TABLE2
UNPIVOT (COLVALUE FOR FIELD IN (COL1, COL2, COL3, COL4, COL5)) AS UPVT
)
SELECT T1.ID, T1.FIELD, T1.COLVALUE AS T1_VALUE, T2.COLVALUE AS T2_VALUE
FROM T1_ROWS AS T1
INNER JOIN T2_ROWS AS T2
ON T1.ID = T2.ID
AND T1.FIELD = T2.FIELD
WHERE T1.COLVALUE <> T2.COLVALUE
DROP TABLE #TABLE1
DROP TABLE #TABLE2
A significant caveat is in order here. You''d have to realize that just having the specific columns that differ in one case could be completely different the next time, so if you were to use this data to feed a web app or a report, you could have column name problems, so my approach was slightly different for precisely this reason.
While there is a way to pivot my results back to what you're looking for, you would trouble the moment there's more than one unique ID value, as then you'd have to have columns for ALL 5 potential mismatches, as yoiu can't have differing column names on each output record.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 12, 2012 at 8:12 am
Will something like this work for what you need?
USE ProofOfConcept;
IF OBJECT_ID(N'tempdb..#T') IS NOT NULL
DROP TABLE #T;
CREATE TABLE #T
(ID INT IDENTITY
PRIMARY KEY,
ParentID INT,
Col1 VARCHAR(25),
Col2 VARCHAR(25),
Col3 VARCHAR(25),
Col4 VARCHAR(25),
Col5 VARCHAR(25));
INSERT INTO #T
(ParentID, Col1, Col2, Col3, Col4, Col5)
VALUES (NULL, 'This', 'That', 'or', 'yes', 'me'),
(1, 'This', 'that', 'not', 'yes', 'you');
SELECT T.ID,
T.ParentID,
NULLIF(T.Col1, Parent.Col1) AS Col1,
NULLIF(T.Col2, Parent.Col2) AS Col2,
NULLIF(T.Col3, Parent.Col3) AS Col3,
NULLIF(T.Col4, Parent.Col4) AS Col4,
NULLIF(T.Col5, Parent.Col5) AS Col5
FROM #T AS T
CROSS APPLY (SELECT *
FROM #T AS T2
WHERE T.ParentID = T2.ID) AS Parent;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 13, 2012 at 4:06 am
Hey guys,
Thanks for the advices but i just did it in my way 🙂 and i think this is worth sharing with our world( and beyond :P) .
Scenario:
Using sql server 2008 r2 , some guys developed a web site for job seekers. However the Admin just wanted to keep track of changing data to see what gets updated in his database. (p.s Not a comfortable client). He wanted to have a history table wich would contain :
Updated Table Name,
Updated Column,
Old value and
New value.
The guys came to me and asked if i could help with SQL because they were frustrated with the client because as they said it would need whole new and too much coding to do.
I decided to use CDC (change data capture) and i just activated it for the DB and then tables and columns.
-- CDC introcudtion : http://msdn.microsoft.com/en-us/library/bb522489(v=sql.105).aspx ----
the problem was that in those sys tables created for CDC i couldnt pick only the updated column even though __$operation column would tell me than 3 is the old value and 4 is new one , i didnt need whole row i needed only changed columns.
After that i came acrros __$update_mask, an varbinary column that tells us which column was updated
Using sys.fn_cdc_is_bit_set(column_ordinal,__$Update_mask) i was able to find which column was updated.
the next problem was that i didnt know the column because the function below gave me 1 if the column was updated and 0 if not.
the problem was that in column_ordinal parameter in the fucntion i had to type the number e.g 1 the function would check only column1, then 2,3...n
and after that i just did something like this and it works but it needs some fine tuning and error management :
declare @table table (id int identity(1,1), has int)
declare @tabelat table (id int identity(1,1),Emri varchar(100))
insert into @tabelat select table_name from information_schema.tables where
table_schema='cdc' and table_name like 'dbo%' -- now i know wich tables i have in cdc
declare @loop int,@start int, @tbemri varchar(200)
set @loop =(select count(*) from @tabelat) -- how many tables there are?
--print @loop
set @start =1
while (@start<= @loop)-- check wich ones have data so i dont have to go through all
begin
set @tbemri= ( select Emri from @tabelat where ID = @start)
insert into @table exec ('if exists (select * from cdc.'+@tbemri+') select 1 else select 0')
set @start=@start+1
end -- now i know wich tables i should check
Declare @objID int -- i have to know also the total column number
set @tbemri = (select a.emri from @tabelat a inner join
@table b on a.Id = b.Id where b.has=1) -- has stands for "has data"
Set @start = (select len(@tbemri)-3)
set @objid = (select object_id from cdc.change_tables where capture_instance = left(@tbemri,@start))
-- now i have the object_id and i can go through colums
set @loop =(select max(column_ordinal) from cdc.captured_columns where object_id = @objid)
-- Above i got the max column number of that table
set @start=1
declare @lesh table (id int Identity (1,1), has int)
while (@start<=@loop)
begin
Insert into @lesh
exec('select sys.fn_cdc_is_bit_set('+@start+',__$Update_mask) from cdc.'+@tbemri+' where __$operation =4')
set @start=@start+1
end
-- and after this loop i know the column ordinal number and i know if that column had data change
-- now i just get the column ordinals that had data change
declare @count int set @count = (select max(id) from @lesh where has=1)
declare @colstart int set @colstart= (select min(id) from @lesh where has=1)
declare @Ordinal varchar(50)
declare @kolona varchar(50)
while (@colstart<=@count)
begin
set @ordinal= (select top 1 id from @lesh where has =1 ) -- i have the colum ordinal number
set @kolona = ( select column_name from cdc.captured_columns where
object_id=@objid and column_ordinal = @ordinal) -- now i have the column name too
-- begin the insertion to my history table
-- @tbemri = TABLE NAME
-- @kolona = Column NAME
-- Field 3 gives me the old value (__$operation=3)
-- Field 4 gives me the new value (__$operation=4)
insert into historia
exec('Select '''+@tbemri+''','''+@kolona+''',
(select '+@kolona+' from cdc.'+@tbemri+' where __$operation=3),
(select '+@kolona+' from cdc.'+@tbemri+' where __$operation=4)')
delete from @lesh where id=@ordinal
set @colstart=@colstart+1
end
exec ('delete from cdc.'+@tbemri)
-------------------------------------
SORRY FOR THE LONG POST AND MESSY CODE 🙂
btw:
SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 134 ms.
have a sexy day . CHeers
December 13, 2012 at 7:02 am
Ah. Audit trails.
CDC will work for that. There are a number of other options, each with its own pros and cons.
I generally prefer to use triggers that capture the changed columns in a simple XML format. Triggers can capture things like what user ID, that CDC can't. And getting XML to only record the columns that were actually changed is dead easy.
One piece of advice on audit trails and logging: You don't need to record the "new" value. The table already has that. All you need to record is the old value (for Update) or the whole row (for Delete), but you don't need to record Insert at all, nor new values on Updates. Because the table has those values. Saves a ton of storage space.
I even have a bit of code that can automate the trigger creation, and can even automate changing the triggers if the table schema is modified.
Would have mentioned all that earlier, but didn't realize you were looking for an audit log.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 13, 2012 at 9:04 am
Thanx for the advices GSquared,
Fortunately the client is interested only in updated values. And i agree that i dont need new value so im writing this down as a Fine tuning check .
As for the triggers i just dont trust them. I guess im alergic to things that fire without me noticing or without my command ! 😀 . And if you would say that CDC has a DB trigger, well, yes but its slightly different . its a built in functionality so maybe i can deal with that.
Cheers !
December 13, 2012 at 9:23 am
Whatever works for you. As mentioned, they've all got pros and cons.
Edit: So far as I know, CDC doesn't use any triggers. It's basically just an automated log-parser. That's why it's got such low overhead. If you read my articles on audit trails and logging, CDC is classic "passive auditing", as per the articles.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 13, 2012 at 9:33 am
to be honest this is the first time im benefiting from CDC , and i discovered it yesterday. I said DB trigger because i just simply saw it under the database trrigers tree 🙂 and i didnt check on it.
Anyways , an old quote of my country says : study as you live !
So it was a nice conversation and thankx for everything, see you in other posts 🙂
December 13, 2012 at 9:34 am
Cool biz.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply