August 5, 2011 at 9:29 am
Hi All,
I have a query. I need to create a script which could do the following :
I have a table which have following columns:
id, name, version, FirstName, SirName, Product, ProductCode
I want to compare the rows of this table which have same ID and current version with the previous version.
I want to store the results of comparison in the following table:
Create table (
Column_Changed varchar(50),
Oldvalue int
NewValue int
)
This above table should store the results of comparison of two rows that i compared above.
Please help me by providing the sql scipt for this.
Thanks alot in advance.
August 5, 2011 at 10:16 am
To get a tested reply to your question, please, sample data and expected results from that sample data.
To do so quickly and easily click on the first link in my signature block to learn how to do so using the T-SQL statements contained in the article.
August 5, 2011 at 10:48 am
Thanks Ron for guiding me.
Following is my question:
/****************table which contains the data to compare**********/
Create table test_data
(
id int,
FirstName varchar(50),
version int,
Product varchar(100),
Area varchar(20),
Price int,
quantity int,
Amount int
)
/******************data in the table****************/
INSERT INTO [dbo].[test_data]
([id],[FirstName] ,[version] ,[Product],[Area],[Price] ,[quantity],[Amount])
VALUES
(100,'Ne',0,'Comb','test',100,2,200)
INSERT INTO [dbo].[test_data]
([id],[FirstName] ,[version] ,[Product],[Area],[Price] ,[quantity],[Amount])
VALUES
(100,'Ne',1,'Comb','test',101,2,201)
INSERT INTO [dbo].[test_data]
([id],[FirstName] ,[version] ,[Product],[Area],[Price] ,[quantity],[Amount])
VALUES
(200,'Ne',0,'Biscuits','India',120,3,300)
INSERT INTO [dbo].[test_data]
([id],[FirstName] ,[version] ,[Product],[Area],[Price] ,[quantity],[Amount])
VALUES
(200,'Ne',1,'Biscuits','India',220,4,320)
/*******************/
Now as we see in the above data for ID : 100, there are two version 0 & 1, so i would need to compare these two and similarly for ID 200, i need to compare version 1 & 0
/*********************OutPut table*************/
Create table Output
(
ID int,
Column_Changed varchar(100),
Old_Value varchar(100),
New_Value varchar(100)
)
I need to following output after comparing the data of table test_Data in the Output table as follows
select * from Output should return the following
ID Column_Changed Old_Value New_Value
100 Price 100 101
100 Amount 200 201
200 Price 120 220
200 Quantity 3 4
200 Amount 300 320
I hope this clarifies. Please give me sql script to do this.
August 5, 2011 at 11:59 am
Thank you for providing ready to use sample data. Made it much easier than just based on the verbal description...:-D
It is assumed that the version number will be ascendign without gaps. Otherwise there would be a ROW_Number() required.
;WITH cte as
(
SELECT id,version, Column_Changed,Value
FROM
(SELECT id,
[version],
cast(product as sql_variant) AS product,
cast(area as sql_variant) AS area,
cast(price as sql_variant) AS price,
cast(quantity as sql_variant) AS quantity,
cast(amount as sql_variant) AS amount
FROM [dbo].[test_data]
) p
UNPIVOT
(Value FOR Column_Changed IN
(product, area, price, quantity, amount)
)AS unpvt
)
SELECT cte1.id,cte1.Column_Changed,cte1.Value AS Old_Value, cte2.Value AS New_Value
FROM cte cte1
INNER JOIN cte cte2
ON cte1.id=cte2.id
AND cte1.version =cte2.version -1
AND cte1.Column_Changed =cte2.Column_Changed
AND cte1.VALUE<>cte2.value
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply