February 9, 2011 at 5:02 am
Hi,
i have a table like below
Create Table tab1
(
RefNo varchar(10),
Version int,
details varchar(200),
Priority int,
Nameofentity varchar(10),
impact int,
category varchar(20),
department varchar(30),
Sectionvarchar(10)
)
Insert into tab1 values ('GB_HQ1768',1,'HQ Expansion',1,'Infosec',2,'COBL','IT','APPS')
Insert into tab1 values ('GB_HQ1768',2,'HQ Expansion',2,'Prime',2,'COBT','IT','TEST')
How to Compare Two Rows and find what are all the columns are got changed for the each reference number 'GB_HQ1768'.
actually i have around 25 columns in the table and need to list all the columns that are got changed.
In the above example Nameofentity,category and Section got changed for the Reference 'GB_HQ1768', so i should have a result set like
ReferenceNo ColumnsChanged
GB_HQ1768 Nameofentity
GB_HQ1768 category
GB_HQ1768 Section
February 9, 2011 at 6:34 am
Will you be supplying only one reference number at a time ? and will there be only 2 rows per reference number?
February 9, 2011 at 7:07 am
This should get you started:
;
WITH RefNos(RefNo)
AS (SELECT DISTINCT
RefNo
FROM dbo.tab1)
SELECT RefNo,
Row.node.value('local-name(.)', 'varchar(100)') AS ColChanged
FROM RefNos
CROSS APPLY (SELECT (SELECT NULLIF(T1.details, T2.details) AS Details,
NULLIF(T1.Priority, T2.Priority) AS Priority,
NULLIF(T1.Nameofentity,
T2.Nameofentity) AS NameOfEntity,
NULLIF(T1.impact, T2.impact) AS Impact,
NULLIF(T1.category, T2.category) AS Category,
NULLIF(T1.department, T2.department) AS Department,
NULLIF(T1.Section, T2.Section) AS Section
FROM dbo.tab1 AS T1
INNER JOIN dbo.tab1 AS T2
ON T1.RefNo = T2.RefNo
AND T1.Version = T2.Version - 1
WHERE T1.RefNo = RefNos.RefNo
FOR
XML PATH,
TYPE) AS XMLVersion) AS Sub
CROSS APPLY XMLVersion.nodes('/row/*') Row (Node);
You can add row version numbers to the query pretty easily. You could even query To/From values this way pretty easily.
- 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
February 9, 2011 at 7:10 am
How about this?
SET NOCOUNT ON
DECLARE @RefNo VARCHAR(10)
SELECT @RefNo = 'GB_HQ1768'
;WITH CTE AS
(
SELECT RefNo , ColName, Vals
FROM
(
SELECT CAST(RefNo AS VARCHAR(200)) AS RefNo,
CAST(Version AS VARCHAR(200)) AS Version,
CAST(details AS VARCHAR(200)) AS details,
CAST(Priority AS VARCHAR(200)) AS Priority,
CAST(Nameofentity AS VARCHAR(200)) AS Nameofentity,
CAST(impact AS VARCHAR(200)) AS impact,
CAST(category AS VARCHAR(200)) AS category,
CAST(department AS VARCHAR(200)) AS department,
CAST(Section AS VARCHAR(200)) AS Section
FROM TestDatatable
WHERE RefNo = @RefNo
) PIVOT_TABLE
UNPIVOT
(
Vals FOR ColName IN (Version,details,Priority,Nameofentity,impact,category,department,Section)
) PIVOT_HANDLE
)
SELECT DISTINCT RefNo , ColName
FROM CTE
GROUP BY RefNo , ColName , Vals
HAVING(COUNT(*)) = 1
ORDER BY ColName
{Edit : Added @RefNo variable to make the query work with any RefNo values}
February 9, 2011 at 7:17 am
Gus, i think, you have missed adding Version column to your inner select.
February 9, 2011 at 7:25 am
ColdCoffee (2/9/2011)
Gus, i think, you have missed adding Version column to your inner select.
The original request didn't include the version number in the results. I try to match the exact requirements on these things.
As I indicated, adding in version numbers, or even From and To values, is very easy in this.
- 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
February 9, 2011 at 7:30 am
GSquared (2/9/2011)
ColdCoffee (2/9/2011)
Gus, i think, you have missed adding Version column to your inner select.The original request didn't include the version number in the results. I try to match the exact requirements on these things.
As I indicated, adding in version numbers, or even From and To values, is very easy in this.
Hmmmm, thats right..
February 9, 2011 at 7:35 am
Sharath, if u will have more than RefNo, just remove the WHERE clause in my query ; it will bring out the changed columns for all the RefNos..
February 9, 2011 at 8:52 am
GSquared (2/9/2011)
This should get you started:
;WITH RefNos(RefNo)
AS (SELECT DISTINCT
RefNo
FROM dbo.tab1)
SELECT RefNo,
Row.node.value('local-name(.)', 'varchar(100)') AS ColChanged
FROM RefNos
CROSS APPLY (SELECT (SELECT NULLIF(T1.details, T2.details) AS Details,
NULLIF(T1.Priority, T2.Priority) AS Priority,
NULLIF(T1.Nameofentity,
T2.Nameofentity) AS NameOfEntity,
NULLIF(T1.impact, T2.impact) AS Impact,
NULLIF(T1.category, T2.category) AS Category,
NULLIF(T1.department, T2.department) AS Department,
NULLIF(T1.Section, T2.Section) AS Section
FROM dbo.tab1 AS T1
INNER JOIN dbo.tab1 AS T2
ON T1.RefNo = T2.RefNo
AND T1.Version = T2.Version - 1
WHERE T1.RefNo = RefNos.RefNo
FOR
XML PATH,
TYPE) AS XMLVersion) AS Sub
CROSS APPLY XMLVersion.nodes('/row/*') Row (Node);
You can add row version numbers to the query pretty easily. You could even query To/From values this way pretty easily.
Thanks for the Query, really i was breaking my head on this,
How do i add the FRom and Two values to the Query. i think that will be really help full to me.
i also have a column called modified by in the table can i inclued that to know who has modified it
thanks once again in advance
February 10, 2011 at 2:56 am
Please some one help me to modify the above Query to get From and TO values with the Version
February 10, 2011 at 5:50 am
Please some one help me to modify the above Query to get From and TO values with the Version
February 10, 2011 at 11:57 am
Try this:
;
WITH RefNos(RefNo)
AS (SELECT DISTINCT
RefNo
FROM dbo.tab1),
DataFrom
AS (SELECT RefNo,
Row.node.value('local-name(.)', 'varchar(100)') AS ColChanged,
Row.node.value('./text()[1]', 'varchar(100)') AS ChangedFrom
FROM RefNos
CROSS APPLY (SELECT (SELECT NULLIF(T1.details,
T2.details) AS Details,
NULLIF(T1.Priority,
T2.Priority) AS Priority,
NULLIF(T1.Nameofentity,
T2.Nameofentity) AS NameOfEntity,
NULLIF(T1.impact,
T2.impact) AS Impact,
NULLIF(T1.category,
T2.category) AS Category,
NULLIF(T1.department,
T2.department) AS Department,
NULLIF(T1.Section,
T2.Section) AS Section
FROM dbo.tab1 AS T1
INNER JOIN dbo.tab1 AS T2
ON T1.RefNo = T2.RefNo
AND T1.Version = T2.Version
- 1
WHERE T1.RefNo = RefNos.RefNo
FOR
XML PATH,
TYPE) AS XMLVersion) AS Sub
CROSS APPLY XMLVersion.nodes('/row/*') Row (Node)),
DataTo
AS (SELECT RefNo,
Row.node.value('local-name(.)', 'varchar(100)') AS ColChanged,
Row.node.value('./text()[1]', 'varchar(100)') AS ChangedTo
FROM RefNos
CROSS APPLY (SELECT (SELECT NULLIF(T2.details,
T1.details) AS Details,
NULLIF(T2.Priority,
T1.Priority) AS Priority,
NULLIF(T2.Nameofentity,
T1.Nameofentity) AS NameOfEntity,
NULLIF(T2.impact,
T1.impact) AS Impact,
NULLIF(T2.category,
T1.category) AS Category,
NULLIF(T2.department,
T1.department) AS Department,
NULLIF(T2.Section,
T1.Section) AS Section
FROM dbo.tab1 AS T1
INNER JOIN dbo.tab1 AS T2
ON T1.RefNo = T2.RefNo
AND T1.Version = T2.Version
- 1
WHERE T1.RefNo = RefNos.RefNo
FOR
XML PATH,
TYPE) AS XMLVersion) AS Sub
CROSS APPLY XMLVersion.nodes('/row/*') Row (Node))
SELECT DataFrom.*,
DataTo.ChangedTo
FROM DataFrom
INNER JOIN DataTo
ON DataFrom.RefNo = DataTo.RefNo
AND DataFrom.ColChanged = DataTo.ColChanged ;
- 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
February 10, 2011 at 12:28 pm
How about this?
SET NOCOUNT ON
GO
DECLARE @RefNo VARCHAR(10)
SELECT @RefNo = 'GB_HQ1768'
;WITH CTE AS
(
SELECT RefNo,Version , ColName, Vals
FROM
(
SELECT CAST(RefNo AS VARCHAR(200)) AS RefNo,
CAST(Version AS VARCHAR(200)) AS Version,
CAST(details AS VARCHAR(200)) AS details,
CAST(Priority AS VARCHAR(200)) AS Priority,
CAST(Nameofentity AS VARCHAR(200)) AS Nameofentity,
CAST(impact AS VARCHAR(200)) AS impact,
CAST(category AS VARCHAR(200)) AS category,
CAST(department AS VARCHAR(200)) AS department,
CAST(Section AS VARCHAR(200)) AS Section
FROM TestDatatable
WHERE RefNo = @RefNo
) UNPIVOT_TABLE
UNPIVOT
(
Vals FOR ColName IN (details,Priority,Nameofentity,impact,category,department,Section)
) UNPIVOT_HANDLE
)
SELECT RefNo , ColName , [1] AS [FromValue] , [2] AS [ToValue]
FROM
(
SELECT RefNo,Version , ColName, Vals
FROM CTE
) PIVOT_TABLE
PIVOT
(
MAX(Vals) FOR Version IN ([1],[2])
)PIVOT_HANDLE
WHERE [1] <> [2]
June 7, 2012 at 1:26 pm
Why was 'Version' column added? Is there a way to get the column values without using it?
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply