How toGet column name with changing value in SQL-server

Question

How toGet column name with changing value in SQL-server

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Y.B. SSChampion Points: 11642 More actions · Answer 1

saroj9958 (4/22/2016)
HI all,
I think I should be more clear on the question
here is the situation
EngineerPostalCodeFTEFieldWorkEfficiencyAllowCMDistrictNameAllowPMSCENARIOid
100234BE0.01.01 District Wallony 0123
100234BE10820.50.51 District Wallony 1124
100235BE10820.01.01 District Wallony 0123
100235BE10820.51.01 District Wallony 1124
SO here you can see we have scenario(123,124) and I need a comparison between two scenario if value of any field is changing in below format:
Engineer column?Name oldScenrio newScenrio
100234 PostalCode BE BE1082
100234 FTEFieldWork 0.0 0.5
100234 Efficiency 1.0 0.5
100234 AllowPM 0 1
100235 FTEFieldWork 0.0 0.5
100235 AllowPM 0 1
FYI-I have a composite key in this table with engineer and Scenario.
Please let me know in case you need more clarification. and I need it urgent basis so request you guy's to Please help me out.
Thanks in Advance.
And feel sorry If I hurt anyone with confusing question.

At this point I have to think that you are not reading any of the responses. J has provided links twice, one on how to post and another related to your problem...yet you still haven't provided the required information. Also if you would have looked at my code you would have realized that it does exactly what you are asking. With only a few minor changes...

IF OBJECT_ID('tempdb.dbo.#LogTable', 'U') IS NOT NULL

DROP TABLE #LogTable

CREATE TABLE #LogTable (Engineer INT, PostalCode VARCHAR(6), FTEFieldWork NUMERIC(2,1), Efficiency NUMERIC(2,1), AllowCM BIT, DistrictName VARCHAR(100), AllowPM BIT, ScenarioID INT)

INSERT INTO #LogTable (Engineer, PostalCode, FTEFieldWork, Efficiency, AllowCM, DistrictName, AllowPM, ScenarioID)

VALUES

(100234, 'BE', 0.0, 1.0, 1, 'District Wallony', 0, 123),

(100234, 'BE1082', 0.5, 0.5, 1, 'District Wallony', 1, 124),

(100235, 'BE1082', 0.0, 1.0, 1, 'District Wallony', 0, 123),

(100235, 'BE1082', 0.5, 1.0, 1, 'District Wallony', 1, 124)

--SELECT * FROM #LogTable

--**************************************************************************************************************

--Now we get a little funky with some dynamic SQL

IF OBJECT_ID('tempdb.dbo.#ChangeTable', 'U') IS NOT NULL

DROP TABLE #ChangeTable

--This table will hold any changes made for all columns of your choice

CREATE TABLE #ChangeTable (Engineer VARCHAR(100), [Column] VARCHAR(256), OldValue VARCHAR(100), NewValue VARCHAR(100))

DECLARE @stmt VARCHAR(MAX)

--These are the columns we want to check for differences. You don't need the Engineer column.

;WITH MyColumns (c) AS (

SELECT * FROM (VALUES('PostalCode'), ('FTEFieldWork'), ('Efficiency'), ('AllowCM'), ('DistrictName'), ('AllowPM')) AS m(x)

)

--Generate a insert statement for each column name above.

SELECT @stmt = (

(SELECT

'INSERT INTO #ChangeTable (Engineer, [Column], OldValue, NewValue)

SELECT x.Engineer, x.FieldName, x.OldValue, x.NewValue FROM (

SELECT

Engineer,

' + '''' + c + '''' + ' AS FieldName,

CAST(LAG(l.' + c + ') OVER (PARTITION BY l.Engineer ORDER BY ScenarioID) AS VARCHAR(100)) AS OldValue,

CAST(l.' + c + ' AS VARCHAR(100)) AS NewValue,

CASE WHEN l.' + c + ' <> LAG(l.' + c + ') OVER (PARTITION BY l.Engineer ORDER BY ScenarioID) THEN 1 ELSE 0 END AS HasChanged

FROM

#LogTable l

) x

WHERE x.HasChanged = 1 ' + CHAR(13) + CHAR(13)

FROM MyColumns FOR XML PATH(''),TYPE).value('.', 'varchar(MAX)')

)

--PRINT @stmt

EXEC (@stmt)

SELECT * FROM #ChangeTable ORDER BY Engineer

SELECT quote FROM brain WHERE original = 1
0 rows returned

saroj9958 Mr or Mrs. 500 Points: 598 More actions · Answer 2

Thanks for the Solution:) Its really works good but we have some Null values are there also in the table and we have to compare rows with null values also..can I have any Idea for this.

Thanks in Advance

Saroj

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

saroj9958 (5/4/2016)
can I have any Idea for this.
Saroj

yes, I am sure you can.

but before this can be easily delivered......can you please provide sample table scripts/insert data/expected results....examples and links have already been provided.

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

saroj9958 Mr or Mrs. 500 Points: 598 More actions · Answer 4

Hi,

Please find Attached Excel sheet where I am comparing two rows and getting result..FYI there I can compare if null values are not avilable but I need to compare with null values also.

Thanks in advance.