Compare to rows and find what columns changedHow can I remove duplicate rows?Best way to get identity of...

It took me a lot of time to make this, pls like. (YouTube Comments #1)

4 Spheres all touching each other??

How to avoid being sexist when trying to employ someone to function in a very sexist environment?

Where was Karl Mordo in Infinity War?

Find the number of ways to express 1050 as sum of consecutive integers

How to properly claim credit for peer review?

Why is this code uniquely decodable?

Meth dealer reference in Family Guy

F1 visa even for a three-week course?

What is Crew Dragon approaching in this picture?

Emit zero-width bash prompt sequence from external binary

Do my Windows system binaries contain sensitive information?

What's the purpose of these copper coils with resitors inside them in A Yamaha RX-V396RDS amplifier?

Why didn't Eru and/or the Valar intervene when Sauron corrupted Númenor?

What is the wife of a henpecked husband called?

What's a good word to describe a public place that looks like it wouldn't be rough?

Do any poskim exempt 13-20-year-olds from Mussaf?

What is the meaning of "pick up" in this sentence?

Metadata API deployments are failing in Spring '19

How should I state my MS degree in my CV when it was in practice a joint-program?

How do we edit a novel that's written by several people?

Is my plan for fixing my water heater leak bad?

Do commercial flights continue with an engine out?

What's the rationale behind the objections to these measures against human trafficking?



Compare to rows and find what columns changed


How can I remove duplicate rows?Best way to get identity of inserted row?Add a column with a default value to an existing table in SQL ServerHow to check if a column exists in a SQL Server table?What is the difference between varchar and nvarchar?How to concatenate text from multiple rows into a single text string in SQL server?Inserting multiple rows in a single SQL query?Altering a column: null to not nullFind all tables containing column with specified name - MS SQL ServerCompare two rows of a table and find which columns are changed













0















CREATE TABLE #mytable (

    [Id] [int] IDENTITY(1,1) NOT NULL,

    [Reqid] [int] NOT NULL,

    [Name] [varchar](150) NULL,

    [IDNo] [char](20) NOT NULL,

    [DOB] [char](10) NULL,

    [Nationality] [char](2) NULL,

    [BasicGroup] [char](3) NULL,

    [CorporateStatus] [char](1) NULL,

    [IndustrialSector] [char](6) NULL,

    [ResidencyStatus] [char](1) NULL,

    [Gender] [char](1) NULL,

    [Income] [char](20) NULL,

    [Occupation] [char](4) NULL,

    [EmpName] [varchar](150) NULL,

    [EmpSec] [char](5) NULL,

    [EmpTyp] [char](3) NULL,

    [Postcode] [char](5) NULL,

    [State] [char](2) NULL,

    [Country] [char](2) NULL,

    [FQ_CRE_TMS] [datetime] NULL,

    [RowNum] int ,

) ON [PRIMARY]





set identity_insert #mytable ON

INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14489,170916258,'TEST3_2',050505050505,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'YOLO','L1000',113,NULL,NULL,NULL,'2019-02-28 21:32:48.247',1);

INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14487,170916258,'TEST2',050505050505,NULL,NULL,NULL,NULL,NULL,NULL,'P',8891126,2171,NULL,NULL,NULL,NULL,NULL,NULL,'2019-02-28 21:29:56.857',2);

INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14442,170916244,'CUST3_2',050505050505,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'YOLO',NULL,NULL,01540,NULL,NULL,'2019-02-28 15:02:33.993',3);

INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14483,170916258,'LALA',100101015698,NULL,NULL,NULL,NULL,'01263',NULL,'L',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-02-28 21:16:42.293',1);

INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14440,170916244,'CUSTOMER1_2',100101015698,NULL,NULL,NULL,'M',NULL,'N',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-02-28 15:01:24.713',2);

INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14485,170916258,'CUST3_6',100202025698,NULL,NULL,NULL,NULL,NULL,'F','P',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-02-28 21:25:45.800',1);

INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14484,170916258,'APPLICANT5_2',100202025698,NULL,NULL,NULL,'M','01261','L','L',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-02-28 21:17:24.420',2);

INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14446,170916258,'CUST5_3',100202025698,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,60000,NULL,NULL,'2019-02-28 15:08:49.650',3);

INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14444,170916258,'CUST5_3',100202025698,NULL,NULL,NULL,NULL,NULL,NULL,'L',NULL,NULL,NULL,NULL,NULL,79660,NULL,NULL,'2019-02-28 15:07:01.790',4);

INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14486,170916258,'EHH4_4',260404045698,NULL,NULL,NULL,NULL,'01279','S','P',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-02-28 21:26:28.967',1);

INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14448,170916258,'CUST4_3',260404045698,NULL,NULL,NULL,NULL,NULL,'L',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-02-28 15:14:35.380',2);

INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14447,170916258,'CUST4_3',260404045698,NULL,NULL,21,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-02-28 15:13:14.670',3);

INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14445,170916258,'CUST4_3',260404045698,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'K1000',NULL,36500,NULL,NULL,'2019-02-28 15:08:24.090',4);

INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14443,170916258,'CUTS4_3',260404045698,NULL,NULL,34,NULL,NULL,NULL,NULL,NULL,NULL,'REWQREREW',NULL,NULL,NULL,NULL,NULL,'2019-02-28 15:06:23.213',5);

INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14488,170916258,'TEST2',780909096398,NULL,NULL,NULL,'L',NULL,'E',NULL,NULL,NULL,'YULU',NULL,NULL,01516,NULL,NULL,'2019-02-28 21:31:46.340',1);

INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14441,170916244,'CUSTOMER2_2',780909096398,NULL,NULL,NULL,NULL,'031XX',NULL,NULL,NULL,NULL,NULL,NULL,NULL,01572,NULL,NULL,'2019-02-28 15:01:57.953',2);



;with #unpivot_step2 as

(

    select reqid, Idno, RowNum, ColName, Vals

    From 

        (

            select  

                    isnull(cast(reqid as nvarchar(255)), '') AS reqid,

                    isnull(cast(Name collate database_default as nvarchar(255)), '') as Name,

                    isnull(cast(IDNo collate database_default as nvarchar(255)), '') as IDNo,

                    isnull(cast(DOB collate database_default as nvarchar(255)), '') as DOB,

                    isnull(cast(BasicGroup collate database_default as nvarchar(255)), '') as BasicGroup,

                    isnull(cast(Nationality collate database_default as nvarchar(255)), '') as Nationality,

                    isnull(cast(CorporateStatus collate database_default as nvarchar(255)), '') as CorporateStatus,

                    isnull(cast(IndustrialSector collate database_default as nvarchar(255)), '') as IndustrialSector,

                    isnull(cast(ResidencyStatus collate database_default as nvarchar(255)), '') as ResidencyStatus,

                    isnull(cast(Gender collate database_default as nvarchar(255)), '') as Gender,

                    isnull(cast(Income collate database_default as nvarchar(255)), '') as Income,

                    isnull(cast(Occupation collate database_default as nvarchar(255)), '') as Occupation,

                    isnull(cast(EmpName collate database_default as nvarchar(255)), '') as EmpName,

                    isnull(cast(EmpSec collate database_default as nvarchar(255)), '') as EmpSec,

                    isnull(cast(EmpTyp collate database_default as nvarchar(255)), '') as EmpTyp,

                    isnull(cast(Postcode collate database_default as nvarchar(255)), '') as Postcode,

                    isnull(cast(State collate database_default as nvarchar(255)), '') as State,

                    isnull(cast(Country collate database_default as nvarchar(255)), '') as Country,

                    row_number() over (partition by idno order by cast(FQ_CRE_TMS as datetime) desc) as RowNum

            from #mytable

        ) unpivot_table

    unpivot

        (

            vals for colname in (Name, DOB, BasicGroup, Nationality, CorporateStatus, 

            IndustrialSector, ResidencyStatus, Gender, Income, Occupation, EmpName, EmpSec,

            EmpTyp, Postcode, State, Country)

        ) unpivot_handle

)

, add_column_name AS

(

    select reqid, IDNo, colname,[2] as [From_Value], [1] as [To_Value]

    FROM 

    (

        select reqid, IDNo, RowNum, colname, vals

        from #unpivot_step2

    ) pivot_table

    pivot

    (

        max (vals) for RowNum in ([1],[2])

    ) pivot_handle

    where [1] <> [2]

)



select distinct p.*, m.Name

into #step1

from add_column_name as p

inner join  #mytable as m

on p.reqid = m.Reqid



select * from #mytable

select * from #step1


This is the result I got after execute the script from above



reqid|IDNo|colname|From_Value|To_Value|Name

170916258|100202025698   |Gender|L|P|APPLICANT5_2

170916258|100202025698   |Gender|L|P|CUST3_6

170916258|100202025698   |Gender|L|P|CUST4_3

170916258|100202025698   |Gender|L|P|CUST5_3

170916258|100202025698   |Gender|L|P|CUTS4_3

170916258|100202025698   |Gender|L|P|EHH4_4

170916258|100202025698   |Gender|L|P|LALA

170916258|100202025698   |Gender|L|P|TEST2

170916258|100202025698   |Gender|L|P|TEST3_2

170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|APPLICANT5_2

170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|CUST3_6

170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|CUST4_3

170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|CUST5_3

170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|CUTS4_3

170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|EHH4_4

170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|LALA

170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|TEST2

170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|TEST3_2

170916258|100202025698   |ResidencyStatus|L|F|APPLICANT5_2

170916258|100202025698   |ResidencyStatus|L|F|CUST3_6

170916258|100202025698   |ResidencyStatus|L|F|CUST4_3

170916258|100202025698   |ResidencyStatus|L|F|CUST5_3

170916258|100202025698   |ResidencyStatus|L|F|CUTS4_3

170916258|100202025698   |ResidencyStatus|L|F|EHH4_4

170916258|100202025698   |ResidencyStatus|L|F|LALA

170916258|100202025698   |ResidencyStatus|L|F|TEST2

170916258|100202025698   |ResidencyStatus|L|F|TEST3_2

170916258|260404045698   |Name|CUST4_3|EHH4_4|APPLICANT5_2

170916258|260404045698   |Name|CUST4_3|EHH4_4|CUST3_6

170916258|260404045698   |Name|CUST4_3|EHH4_4|CUST4_3

170916258|260404045698   |Name|CUST4_3|EHH4_4|CUST5_3

170916258|260404045698   |Name|CUST4_3|EHH4_4|CUTS4_3

170916258|260404045698   |Name|CUST4_3|EHH4_4|EHH4_4

170916258|260404045698   |Name|CUST4_3|EHH4_4|LALA

170916258|260404045698   |Name|CUST4_3|EHH4_4|TEST2

170916258|260404045698   |Name|CUST4_3|EHH4_4|TEST3_2

170916258|260404045698   |ResidencyStatus|L|S|APPLICANT5_2

170916258|260404045698   |ResidencyStatus|L|S|CUST3_6

170916258|260404045698   |ResidencyStatus|L|S|CUST4_3

170916258|260404045698   |ResidencyStatus|L|S|CUST5_3

170916258|260404045698   |ResidencyStatus|L|S|CUTS4_3

170916258|260404045698   |ResidencyStatus|L|S|EHH4_4

170916258|260404045698   |ResidencyStatus|L|S|LALA

170916258|260404045698   |ResidencyStatus|L|S|TEST2

170916258|260404045698   |ResidencyStatus|L|S|TEST3_2

170916258|50505050505   |Name|TEST2|TEST3_2|APPLICANT5_2

170916258|50505050505   |Name|TEST2|TEST3_2|CUST3_6

170916258|50505050505   |Name|TEST2|TEST3_2|CUST4_3

170916258|50505050505   |Name|TEST2|TEST3_2|CUST5_3

170916258|50505050505   |Name|TEST2|TEST3_2|CUTS4_3

170916258|50505050505   |Name|TEST2|TEST3_2|EHH4_4

170916258|50505050505   |Name|TEST2|TEST3_2|LALA

170916258|50505050505   |Name|TEST2|TEST3_2|TEST2

170916258|50505050505   |Name|TEST2|TEST3_2|TEST3_2


‌‌



H‌ow can I get as below result?



+-----------+--------------+------------------+--------------+----------+---------+------------+----------+

|   reqid   |     IDNo     |     colname      |  From_Value  | To_Value |  Name   | BasicGroup | Postcode |

+-----------+--------------+------------------+--------------+----------+---------+------------+----------+

| 170916258 | 100202025698 | Gender           | L            | P        | CUST3_6 | NULL       |    60000 |

| 170916258 | 100202025698 | Name             | APPLICANT5_2 | CUST3_6  | CUST3_6 | NULL       |    60000 |

| 170916258 | 100202025698 | ResidencyStatus  | L            | P        | CUST3_6 | NULL       |    60000 |

| 170916258 | 100202025698 | IndustrialSector | 1261         | NULL     | CUST3_6 | NULL       |    60000 |

+-----------+--------------+------------------+--------------+----------+---------+------------+----------+


colname, From_Value, To_Value is based from rowNum = 1 , 2 from #mytable ( want to get any result that got changes)




  1. Name, BasicGroup, Postcode and other column (gender, DOB, income)[in the result I want] is to find the most update that got result. EG: ID=100202025698, postcode only available in rownum=3 in #mytable and I want show it out.


A‌ny idea how to enhance my script? I want it generally working for every table not for this special case.






sql-server







share|improve this question













migrated from serverfault.com 10 hours ago


This question came from our site for system and network administrators.























    0















    CREATE TABLE #mytable (
    
    [Id] [int] IDENTITY(1,1) NOT NULL,
    
    [Reqid] [int] NOT NULL,
    
    [Name] [varchar](150) NULL,
    
    [IDNo] [char](20) NOT NULL,
    
    [DOB] [char](10) NULL,
    
    [Nationality] [char](2) NULL,
    
    [BasicGroup] [char](3) NULL,
    
    [CorporateStatus] [char](1) NULL,
    
    [IndustrialSector] [char](6) NULL,
    
    [ResidencyStatus] [char](1) NULL,
    
    [Gender] [char](1) NULL,
    
    [Income] [char](20) NULL,
    
    [Occupation] [char](4) NULL,
    
    [EmpName] [varchar](150) NULL,
    
    [EmpSec] [char](5) NULL,
    
    [EmpTyp] [char](3) NULL,
    
    [Postcode] [char](5) NULL,
    
    [State] [char](2) NULL,
    
    [Country] [char](2) NULL,
    
    [FQ_CRE_TMS] [datetime] NULL,
    
    [RowNum] int ,
    
) ON [PRIMARY]
    

    

    
set identity_insert #mytable ON
    
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14489,170916258,'TEST3_2',050505050505,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'YOLO','L1000',113,NULL,NULL,NULL,'2019-02-28 21:32:48.247',1);
    
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14487,170916258,'TEST2',050505050505,NULL,NULL,NULL,NULL,NULL,NULL,'P',8891126,2171,NULL,NULL,NULL,NULL,NULL,NULL,'2019-02-28 21:29:56.857',2);
    
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14442,170916244,'CUST3_2',050505050505,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'YOLO',NULL,NULL,01540,NULL,NULL,'2019-02-28 15:02:33.993',3);
    
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14483,170916258,'LALA',100101015698,NULL,NULL,NULL,NULL,'01263',NULL,'L',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-02-28 21:16:42.293',1);
    
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14440,170916244,'CUSTOMER1_2',100101015698,NULL,NULL,NULL,'M',NULL,'N',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-02-28 15:01:24.713',2);
    
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14485,170916258,'CUST3_6',100202025698,NULL,NULL,NULL,NULL,NULL,'F','P',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-02-28 21:25:45.800',1);
    
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14484,170916258,'APPLICANT5_2',100202025698,NULL,NULL,NULL,'M','01261','L','L',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-02-28 21:17:24.420',2);
    
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14446,170916258,'CUST5_3',100202025698,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,60000,NULL,NULL,'2019-02-28 15:08:49.650',3);
    
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14444,170916258,'CUST5_3',100202025698,NULL,NULL,NULL,NULL,NULL,NULL,'L',NULL,NULL,NULL,NULL,NULL,79660,NULL,NULL,'2019-02-28 15:07:01.790',4);
    
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14486,170916258,'EHH4_4',260404045698,NULL,NULL,NULL,NULL,'01279','S','P',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-02-28 21:26:28.967',1);
    
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14448,170916258,'CUST4_3',260404045698,NULL,NULL,NULL,NULL,NULL,'L',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-02-28 15:14:35.380',2);
    
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14447,170916258,'CUST4_3',260404045698,NULL,NULL,21,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-02-28 15:13:14.670',3);
    
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14445,170916258,'CUST4_3',260404045698,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'K1000',NULL,36500,NULL,NULL,'2019-02-28 15:08:24.090',4);
    
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14443,170916258,'CUTS4_3',260404045698,NULL,NULL,34,NULL,NULL,NULL,NULL,NULL,NULL,'REWQREREW',NULL,NULL,NULL,NULL,NULL,'2019-02-28 15:06:23.213',5);
    
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14488,170916258,'TEST2',780909096398,NULL,NULL,NULL,'L',NULL,'E',NULL,NULL,NULL,'YULU',NULL,NULL,01516,NULL,NULL,'2019-02-28 21:31:46.340',1);
    
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14441,170916244,'CUSTOMER2_2',780909096398,NULL,NULL,NULL,NULL,'031XX',NULL,NULL,NULL,NULL,NULL,NULL,NULL,01572,NULL,NULL,'2019-02-28 15:01:57.953',2);
    

    
;with #unpivot_step2 as
    
(
    
    select reqid, Idno, RowNum, ColName, Vals
    
    From 
    
        (
    
            select  
    
                    isnull(cast(reqid as nvarchar(255)), '') AS reqid,
    
                    isnull(cast(Name collate database_default as nvarchar(255)), '') as Name,
    
                    isnull(cast(IDNo collate database_default as nvarchar(255)), '') as IDNo,
    
                    isnull(cast(DOB collate database_default as nvarchar(255)), '') as DOB,
    
                    isnull(cast(BasicGroup collate database_default as nvarchar(255)), '') as BasicGroup,
    
                    isnull(cast(Nationality collate database_default as nvarchar(255)), '') as Nationality,
    
                    isnull(cast(CorporateStatus collate database_default as nvarchar(255)), '') as CorporateStatus,
    
                    isnull(cast(IndustrialSector collate database_default as nvarchar(255)), '') as IndustrialSector,
    
                    isnull(cast(ResidencyStatus collate database_default as nvarchar(255)), '') as ResidencyStatus,
    
                    isnull(cast(Gender collate database_default as nvarchar(255)), '') as Gender,
    
                    isnull(cast(Income collate database_default as nvarchar(255)), '') as Income,
    
                    isnull(cast(Occupation collate database_default as nvarchar(255)), '') as Occupation,
    
                    isnull(cast(EmpName collate database_default as nvarchar(255)), '') as EmpName,
    
                    isnull(cast(EmpSec collate database_default as nvarchar(255)), '') as EmpSec,
    
                    isnull(cast(EmpTyp collate database_default as nvarchar(255)), '') as EmpTyp,
    
                    isnull(cast(Postcode collate database_default as nvarchar(255)), '') as Postcode,
    
                    isnull(cast(State collate database_default as nvarchar(255)), '') as State,
    
                    isnull(cast(Country collate database_default as nvarchar(255)), '') as Country,
    
                    row_number() over (partition by idno order by cast(FQ_CRE_TMS as datetime) desc) as RowNum
    
            from #mytable
    
        ) unpivot_table
    
    unpivot
    
        (
    
            vals for colname in (Name, DOB, BasicGroup, Nationality, CorporateStatus, 
    
            IndustrialSector, ResidencyStatus, Gender, Income, Occupation, EmpName, EmpSec,
    
            EmpTyp, Postcode, State, Country)
    
        ) unpivot_handle
    
)
    
, add_column_name AS
    
(
    
    select reqid, IDNo, colname,[2] as [From_Value], [1] as [To_Value]
    
    FROM 
    
    (
    
        select reqid, IDNo, RowNum, colname, vals
    
        from #unpivot_step2
    
    ) pivot_table
    
    pivot
    
    (
    
        max (vals) for RowNum in ([1],[2])
    
    ) pivot_handle
    
    where [1] <> [2]
    
)
    

    
select distinct p.*, m.Name
    
into #step1
    
from add_column_name as p
    
inner join  #mytable as m
    
on p.reqid = m.Reqid
    

    
select * from #mytable
    
select * from #step1


    This is the result I got after execute the script from above



    reqid|IDNo|colname|From_Value|To_Value|Name
    
170916258|100202025698   |Gender|L|P|APPLICANT5_2
    
170916258|100202025698   |Gender|L|P|CUST3_6
    
170916258|100202025698   |Gender|L|P|CUST4_3
    
170916258|100202025698   |Gender|L|P|CUST5_3
    
170916258|100202025698   |Gender|L|P|CUTS4_3
    
170916258|100202025698   |Gender|L|P|EHH4_4
    
170916258|100202025698   |Gender|L|P|LALA
    
170916258|100202025698   |Gender|L|P|TEST2
    
170916258|100202025698   |Gender|L|P|TEST3_2
    
170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|APPLICANT5_2
    
170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|CUST3_6
    
170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|CUST4_3
    
170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|CUST5_3
    
170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|CUTS4_3
    
170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|EHH4_4
    
170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|LALA
    
170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|TEST2
    
170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|TEST3_2
    
170916258|100202025698   |ResidencyStatus|L|F|APPLICANT5_2
    
170916258|100202025698   |ResidencyStatus|L|F|CUST3_6
    
170916258|100202025698   |ResidencyStatus|L|F|CUST4_3
    
170916258|100202025698   |ResidencyStatus|L|F|CUST5_3
    
170916258|100202025698   |ResidencyStatus|L|F|CUTS4_3
    
170916258|100202025698   |ResidencyStatus|L|F|EHH4_4
    
170916258|100202025698   |ResidencyStatus|L|F|LALA
    
170916258|100202025698   |ResidencyStatus|L|F|TEST2
    
170916258|100202025698   |ResidencyStatus|L|F|TEST3_2
    
170916258|260404045698   |Name|CUST4_3|EHH4_4|APPLICANT5_2
    
170916258|260404045698   |Name|CUST4_3|EHH4_4|CUST3_6
    
170916258|260404045698   |Name|CUST4_3|EHH4_4|CUST4_3
    
170916258|260404045698   |Name|CUST4_3|EHH4_4|CUST5_3
    
170916258|260404045698   |Name|CUST4_3|EHH4_4|CUTS4_3
    
170916258|260404045698   |Name|CUST4_3|EHH4_4|EHH4_4
    
170916258|260404045698   |Name|CUST4_3|EHH4_4|LALA
    
170916258|260404045698   |Name|CUST4_3|EHH4_4|TEST2
    
170916258|260404045698   |Name|CUST4_3|EHH4_4|TEST3_2
    
170916258|260404045698   |ResidencyStatus|L|S|APPLICANT5_2
    
170916258|260404045698   |ResidencyStatus|L|S|CUST3_6
    
170916258|260404045698   |ResidencyStatus|L|S|CUST4_3
    
170916258|260404045698   |ResidencyStatus|L|S|CUST5_3
    
170916258|260404045698   |ResidencyStatus|L|S|CUTS4_3
    
170916258|260404045698   |ResidencyStatus|L|S|EHH4_4
    
170916258|260404045698   |ResidencyStatus|L|S|LALA
    
170916258|260404045698   |ResidencyStatus|L|S|TEST2
    
170916258|260404045698   |ResidencyStatus|L|S|TEST3_2
    
170916258|50505050505   |Name|TEST2|TEST3_2|APPLICANT5_2
    
170916258|50505050505   |Name|TEST2|TEST3_2|CUST3_6
    
170916258|50505050505   |Name|TEST2|TEST3_2|CUST4_3
    
170916258|50505050505   |Name|TEST2|TEST3_2|CUST5_3
    
170916258|50505050505   |Name|TEST2|TEST3_2|CUTS4_3
    
170916258|50505050505   |Name|TEST2|TEST3_2|EHH4_4
    
170916258|50505050505   |Name|TEST2|TEST3_2|LALA
    
170916258|50505050505   |Name|TEST2|TEST3_2|TEST2
    
170916258|50505050505   |Name|TEST2|TEST3_2|TEST3_2


    ‌‌



    H‌ow can I get as below result?



    +-----------+--------------+------------------+--------------+----------+---------+------------+----------+
    
|   reqid   |     IDNo     |     colname      |  From_Value  | To_Value |  Name   | BasicGroup | Postcode |
    
+-----------+--------------+------------------+--------------+----------+---------+------------+----------+
    
| 170916258 | 100202025698 | Gender           | L            | P        | CUST3_6 | NULL       |    60000 |
    
| 170916258 | 100202025698 | Name             | APPLICANT5_2 | CUST3_6  | CUST3_6 | NULL       |    60000 |
    
| 170916258 | 100202025698 | ResidencyStatus  | L            | P        | CUST3_6 | NULL       |    60000 |
    
| 170916258 | 100202025698 | IndustrialSector | 1261         | NULL     | CUST3_6 | NULL       |    60000 |
    
+-----------+--------------+------------------+--------------+----------+---------+------------+----------+


    colname, From_Value, To_Value is based from rowNum = 1 , 2 from #mytable ( want to get any result that got changes)




    1. Name, BasicGroup, Postcode and other column (gender, DOB, income)[in the result I want] is to find the most update that got result. EG: ID=100202025698, postcode only available in rownum=3 in #mytable and I want show it out.


    A‌ny idea how to enhance my script? I want it generally working for every table not for this special case.






    sql-server







    share|improve this question













    migrated from serverfault.com 10 hours ago


    This question came from our site for system and network administrators.





















      0












      0








      0








      CREATE TABLE #mytable (
      
    [Id] [int] IDENTITY(1,1) NOT NULL,
      
    [Reqid] [int] NOT NULL,
      
    [Name] [varchar](150) NULL,
      
    [IDNo] [char](20) NOT NULL,
      
    [DOB] [char](10) NULL,
      
    [Nationality] [char](2) NULL,
      
    [BasicGroup] [char](3) NULL,
      
    [CorporateStatus] [char](1) NULL,
      
    [IndustrialSector] [char](6) NULL,
      
    [ResidencyStatus] [char](1) NULL,
      
    [Gender] [char](1) NULL,
      
    [Income] [char](20) NULL,
      
    [Occupation] [char](4) NULL,
      
    [EmpName] [varchar](150) NULL,
      
    [EmpSec] [char](5) NULL,
      
    [EmpTyp] [char](3) NULL,
      
    [Postcode] [char](5) NULL,
      
    [State] [char](2) NULL,
      
    [Country] [char](2) NULL,
      
    [FQ_CRE_TMS] [datetime] NULL,
      
    [RowNum] int ,
      
) ON [PRIMARY]
      

      

      
set identity_insert #mytable ON
      
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14489,170916258,'TEST3_2',050505050505,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'YOLO','L1000',113,NULL,NULL,NULL,'2019-02-28 21:32:48.247',1);
      
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14487,170916258,'TEST2',050505050505,NULL,NULL,NULL,NULL,NULL,NULL,'P',8891126,2171,NULL,NULL,NULL,NULL,NULL,NULL,'2019-02-28 21:29:56.857',2);
      
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14442,170916244,'CUST3_2',050505050505,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'YOLO',NULL,NULL,01540,NULL,NULL,'2019-02-28 15:02:33.993',3);
      
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14483,170916258,'LALA',100101015698,NULL,NULL,NULL,NULL,'01263',NULL,'L',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-02-28 21:16:42.293',1);
      
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14440,170916244,'CUSTOMER1_2',100101015698,NULL,NULL,NULL,'M',NULL,'N',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-02-28 15:01:24.713',2);
      
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14485,170916258,'CUST3_6',100202025698,NULL,NULL,NULL,NULL,NULL,'F','P',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-02-28 21:25:45.800',1);
      
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14484,170916258,'APPLICANT5_2',100202025698,NULL,NULL,NULL,'M','01261','L','L',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-02-28 21:17:24.420',2);
      
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14446,170916258,'CUST5_3',100202025698,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,60000,NULL,NULL,'2019-02-28 15:08:49.650',3);
      
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14444,170916258,'CUST5_3',100202025698,NULL,NULL,NULL,NULL,NULL,NULL,'L',NULL,NULL,NULL,NULL,NULL,79660,NULL,NULL,'2019-02-28 15:07:01.790',4);
      
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14486,170916258,'EHH4_4',260404045698,NULL,NULL,NULL,NULL,'01279','S','P',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-02-28 21:26:28.967',1);
      
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14448,170916258,'CUST4_3',260404045698,NULL,NULL,NULL,NULL,NULL,'L',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-02-28 15:14:35.380',2);
      
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14447,170916258,'CUST4_3',260404045698,NULL,NULL,21,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-02-28 15:13:14.670',3);
      
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14445,170916258,'CUST4_3',260404045698,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'K1000',NULL,36500,NULL,NULL,'2019-02-28 15:08:24.090',4);
      
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14443,170916258,'CUTS4_3',260404045698,NULL,NULL,34,NULL,NULL,NULL,NULL,NULL,NULL,'REWQREREW',NULL,NULL,NULL,NULL,NULL,'2019-02-28 15:06:23.213',5);
      
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14488,170916258,'TEST2',780909096398,NULL,NULL,NULL,'L',NULL,'E',NULL,NULL,NULL,'YULU',NULL,NULL,01516,NULL,NULL,'2019-02-28 21:31:46.340',1);
      
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14441,170916244,'CUSTOMER2_2',780909096398,NULL,NULL,NULL,NULL,'031XX',NULL,NULL,NULL,NULL,NULL,NULL,NULL,01572,NULL,NULL,'2019-02-28 15:01:57.953',2);
      

      
;with #unpivot_step2 as
      
(
      
    select reqid, Idno, RowNum, ColName, Vals
      
    From 
      
        (
      
            select  
      
                    isnull(cast(reqid as nvarchar(255)), '') AS reqid,
      
                    isnull(cast(Name collate database_default as nvarchar(255)), '') as Name,
      
                    isnull(cast(IDNo collate database_default as nvarchar(255)), '') as IDNo,
      
                    isnull(cast(DOB collate database_default as nvarchar(255)), '') as DOB,
      
                    isnull(cast(BasicGroup collate database_default as nvarchar(255)), '') as BasicGroup,
      
                    isnull(cast(Nationality collate database_default as nvarchar(255)), '') as Nationality,
      
                    isnull(cast(CorporateStatus collate database_default as nvarchar(255)), '') as CorporateStatus,
      
                    isnull(cast(IndustrialSector collate database_default as nvarchar(255)), '') as IndustrialSector,
      
                    isnull(cast(ResidencyStatus collate database_default as nvarchar(255)), '') as ResidencyStatus,
      
                    isnull(cast(Gender collate database_default as nvarchar(255)), '') as Gender,
      
                    isnull(cast(Income collate database_default as nvarchar(255)), '') as Income,
      
                    isnull(cast(Occupation collate database_default as nvarchar(255)), '') as Occupation,
      
                    isnull(cast(EmpName collate database_default as nvarchar(255)), '') as EmpName,
      
                    isnull(cast(EmpSec collate database_default as nvarchar(255)), '') as EmpSec,
      
                    isnull(cast(EmpTyp collate database_default as nvarchar(255)), '') as EmpTyp,
      
                    isnull(cast(Postcode collate database_default as nvarchar(255)), '') as Postcode,
      
                    isnull(cast(State collate database_default as nvarchar(255)), '') as State,
      
                    isnull(cast(Country collate database_default as nvarchar(255)), '') as Country,
      
                    row_number() over (partition by idno order by cast(FQ_CRE_TMS as datetime) desc) as RowNum
      
            from #mytable
      
        ) unpivot_table
      
    unpivot
      
        (
      
            vals for colname in (Name, DOB, BasicGroup, Nationality, CorporateStatus, 
      
            IndustrialSector, ResidencyStatus, Gender, Income, Occupation, EmpName, EmpSec,
      
            EmpTyp, Postcode, State, Country)
      
        ) unpivot_handle
      
)
      
, add_column_name AS
      
(
      
    select reqid, IDNo, colname,[2] as [From_Value], [1] as [To_Value]
      
    FROM 
      
    (
      
        select reqid, IDNo, RowNum, colname, vals
      
        from #unpivot_step2
      
    ) pivot_table
      
    pivot
      
    (
      
        max (vals) for RowNum in ([1],[2])
      
    ) pivot_handle
      
    where [1] <> [2]
      
)
      

      
select distinct p.*, m.Name
      
into #step1
      
from add_column_name as p
      
inner join  #mytable as m
      
on p.reqid = m.Reqid
      

      
select * from #mytable
      
select * from #step1


      This is the result I got after execute the script from above



      reqid|IDNo|colname|From_Value|To_Value|Name
      
170916258|100202025698   |Gender|L|P|APPLICANT5_2
      
170916258|100202025698   |Gender|L|P|CUST3_6
      
170916258|100202025698   |Gender|L|P|CUST4_3
      
170916258|100202025698   |Gender|L|P|CUST5_3
      
170916258|100202025698   |Gender|L|P|CUTS4_3
      
170916258|100202025698   |Gender|L|P|EHH4_4
      
170916258|100202025698   |Gender|L|P|LALA
      
170916258|100202025698   |Gender|L|P|TEST2
      
170916258|100202025698   |Gender|L|P|TEST3_2
      
170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|APPLICANT5_2
      
170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|CUST3_6
      
170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|CUST4_3
      
170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|CUST5_3
      
170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|CUTS4_3
      
170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|EHH4_4
      
170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|LALA
      
170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|TEST2
      
170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|TEST3_2
      
170916258|100202025698   |ResidencyStatus|L|F|APPLICANT5_2
      
170916258|100202025698   |ResidencyStatus|L|F|CUST3_6
      
170916258|100202025698   |ResidencyStatus|L|F|CUST4_3
      
170916258|100202025698   |ResidencyStatus|L|F|CUST5_3
      
170916258|100202025698   |ResidencyStatus|L|F|CUTS4_3
      
170916258|100202025698   |ResidencyStatus|L|F|EHH4_4
      
170916258|100202025698   |ResidencyStatus|L|F|LALA
      
170916258|100202025698   |ResidencyStatus|L|F|TEST2
      
170916258|100202025698   |ResidencyStatus|L|F|TEST3_2
      
170916258|260404045698   |Name|CUST4_3|EHH4_4|APPLICANT5_2
      
170916258|260404045698   |Name|CUST4_3|EHH4_4|CUST3_6
      
170916258|260404045698   |Name|CUST4_3|EHH4_4|CUST4_3
      
170916258|260404045698   |Name|CUST4_3|EHH4_4|CUST5_3
      
170916258|260404045698   |Name|CUST4_3|EHH4_4|CUTS4_3
      
170916258|260404045698   |Name|CUST4_3|EHH4_4|EHH4_4
      
170916258|260404045698   |Name|CUST4_3|EHH4_4|LALA
      
170916258|260404045698   |Name|CUST4_3|EHH4_4|TEST2
      
170916258|260404045698   |Name|CUST4_3|EHH4_4|TEST3_2
      
170916258|260404045698   |ResidencyStatus|L|S|APPLICANT5_2
      
170916258|260404045698   |ResidencyStatus|L|S|CUST3_6
      
170916258|260404045698   |ResidencyStatus|L|S|CUST4_3
      
170916258|260404045698   |ResidencyStatus|L|S|CUST5_3
      
170916258|260404045698   |ResidencyStatus|L|S|CUTS4_3
      
170916258|260404045698   |ResidencyStatus|L|S|EHH4_4
      
170916258|260404045698   |ResidencyStatus|L|S|LALA
      
170916258|260404045698   |ResidencyStatus|L|S|TEST2
      
170916258|260404045698   |ResidencyStatus|L|S|TEST3_2
      
170916258|50505050505   |Name|TEST2|TEST3_2|APPLICANT5_2
      
170916258|50505050505   |Name|TEST2|TEST3_2|CUST3_6
      
170916258|50505050505   |Name|TEST2|TEST3_2|CUST4_3
      
170916258|50505050505   |Name|TEST2|TEST3_2|CUST5_3
      
170916258|50505050505   |Name|TEST2|TEST3_2|CUTS4_3
      
170916258|50505050505   |Name|TEST2|TEST3_2|EHH4_4
      
170916258|50505050505   |Name|TEST2|TEST3_2|LALA
      
170916258|50505050505   |Name|TEST2|TEST3_2|TEST2
      
170916258|50505050505   |Name|TEST2|TEST3_2|TEST3_2


      ‌‌



      H‌ow can I get as below result?



      +-----------+--------------+------------------+--------------+----------+---------+------------+----------+
      
|   reqid   |     IDNo     |     colname      |  From_Value  | To_Value |  Name   | BasicGroup | Postcode |
      
+-----------+--------------+------------------+--------------+----------+---------+------------+----------+
      
| 170916258 | 100202025698 | Gender           | L            | P        | CUST3_6 | NULL       |    60000 |
      
| 170916258 | 100202025698 | Name             | APPLICANT5_2 | CUST3_6  | CUST3_6 | NULL       |    60000 |
      
| 170916258 | 100202025698 | ResidencyStatus  | L            | P        | CUST3_6 | NULL       |    60000 |
      
| 170916258 | 100202025698 | IndustrialSector | 1261         | NULL     | CUST3_6 | NULL       |    60000 |
      
+-----------+--------------+------------------+--------------+----------+---------+------------+----------+


      colname, From_Value, To_Value is based from rowNum = 1 , 2 from #mytable ( want to get any result that got changes)




      1. Name, BasicGroup, Postcode and other column (gender, DOB, income)[in the result I want] is to find the most update that got result. EG: ID=100202025698, postcode only available in rownum=3 in #mytable and I want show it out.


      A‌ny idea how to enhance my script? I want it generally working for every table not for this special case.






      sql-server







      share|improve this question














      CREATE TABLE #mytable (
      
    [Id] [int] IDENTITY(1,1) NOT NULL,
      
    [Reqid] [int] NOT NULL,
      
    [Name] [varchar](150) NULL,
      
    [IDNo] [char](20) NOT NULL,
      
    [DOB] [char](10) NULL,
      
    [Nationality] [char](2) NULL,
      
    [BasicGroup] [char](3) NULL,
      
    [CorporateStatus] [char](1) NULL,
      
    [IndustrialSector] [char](6) NULL,
      
    [ResidencyStatus] [char](1) NULL,
      
    [Gender] [char](1) NULL,
      
    [Income] [char](20) NULL,
      
    [Occupation] [char](4) NULL,
      
    [EmpName] [varchar](150) NULL,
      
    [EmpSec] [char](5) NULL,
      
    [EmpTyp] [char](3) NULL,
      
    [Postcode] [char](5) NULL,
      
    [State] [char](2) NULL,
      
    [Country] [char](2) NULL,
      
    [FQ_CRE_TMS] [datetime] NULL,
      
    [RowNum] int ,
      
) ON [PRIMARY]
      

      

      
set identity_insert #mytable ON
      
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14489,170916258,'TEST3_2',050505050505,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'YOLO','L1000',113,NULL,NULL,NULL,'2019-02-28 21:32:48.247',1);
      
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14487,170916258,'TEST2',050505050505,NULL,NULL,NULL,NULL,NULL,NULL,'P',8891126,2171,NULL,NULL,NULL,NULL,NULL,NULL,'2019-02-28 21:29:56.857',2);
      
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14442,170916244,'CUST3_2',050505050505,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'YOLO',NULL,NULL,01540,NULL,NULL,'2019-02-28 15:02:33.993',3);
      
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14483,170916258,'LALA',100101015698,NULL,NULL,NULL,NULL,'01263',NULL,'L',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-02-28 21:16:42.293',1);
      
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14440,170916244,'CUSTOMER1_2',100101015698,NULL,NULL,NULL,'M',NULL,'N',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-02-28 15:01:24.713',2);
      
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14485,170916258,'CUST3_6',100202025698,NULL,NULL,NULL,NULL,NULL,'F','P',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-02-28 21:25:45.800',1);
      
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14484,170916258,'APPLICANT5_2',100202025698,NULL,NULL,NULL,'M','01261','L','L',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-02-28 21:17:24.420',2);
      
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14446,170916258,'CUST5_3',100202025698,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,60000,NULL,NULL,'2019-02-28 15:08:49.650',3);
      
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14444,170916258,'CUST5_3',100202025698,NULL,NULL,NULL,NULL,NULL,NULL,'L',NULL,NULL,NULL,NULL,NULL,79660,NULL,NULL,'2019-02-28 15:07:01.790',4);
      
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14486,170916258,'EHH4_4',260404045698,NULL,NULL,NULL,NULL,'01279','S','P',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-02-28 21:26:28.967',1);
      
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14448,170916258,'CUST4_3',260404045698,NULL,NULL,NULL,NULL,NULL,'L',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-02-28 15:14:35.380',2);
      
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14447,170916258,'CUST4_3',260404045698,NULL,NULL,21,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-02-28 15:13:14.670',3);
      
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14445,170916258,'CUST4_3',260404045698,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'K1000',NULL,36500,NULL,NULL,'2019-02-28 15:08:24.090',4);
      
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14443,170916258,'CUTS4_3',260404045698,NULL,NULL,34,NULL,NULL,NULL,NULL,NULL,NULL,'REWQREREW',NULL,NULL,NULL,NULL,NULL,'2019-02-28 15:06:23.213',5);
      
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14488,170916258,'TEST2',780909096398,NULL,NULL,NULL,'L',NULL,'E',NULL,NULL,NULL,'YULU',NULL,NULL,01516,NULL,NULL,'2019-02-28 21:31:46.340',1);
      
INSERT INTO #mytable(id,Reqid,Name,IDNo,DOB,Nationality,BasicGroup,CorporateStatus,IndustrialSector,ResidencyStatus,Gender,Income,Occupation,EmpName,EmpSec,EmpTyp,Postcode,State,Country,FQ_CRE_TMS,RowNum) VALUES (14441,170916244,'CUSTOMER2_2',780909096398,NULL,NULL,NULL,NULL,'031XX',NULL,NULL,NULL,NULL,NULL,NULL,NULL,01572,NULL,NULL,'2019-02-28 15:01:57.953',2);
      

      
;with #unpivot_step2 as
      
(
      
    select reqid, Idno, RowNum, ColName, Vals
      
    From 
      
        (
      
            select  
      
                    isnull(cast(reqid as nvarchar(255)), '') AS reqid,
      
                    isnull(cast(Name collate database_default as nvarchar(255)), '') as Name,
      
                    isnull(cast(IDNo collate database_default as nvarchar(255)), '') as IDNo,
      
                    isnull(cast(DOB collate database_default as nvarchar(255)), '') as DOB,
      
                    isnull(cast(BasicGroup collate database_default as nvarchar(255)), '') as BasicGroup,
      
                    isnull(cast(Nationality collate database_default as nvarchar(255)), '') as Nationality,
      
                    isnull(cast(CorporateStatus collate database_default as nvarchar(255)), '') as CorporateStatus,
      
                    isnull(cast(IndustrialSector collate database_default as nvarchar(255)), '') as IndustrialSector,
      
                    isnull(cast(ResidencyStatus collate database_default as nvarchar(255)), '') as ResidencyStatus,
      
                    isnull(cast(Gender collate database_default as nvarchar(255)), '') as Gender,
      
                    isnull(cast(Income collate database_default as nvarchar(255)), '') as Income,
      
                    isnull(cast(Occupation collate database_default as nvarchar(255)), '') as Occupation,
      
                    isnull(cast(EmpName collate database_default as nvarchar(255)), '') as EmpName,
      
                    isnull(cast(EmpSec collate database_default as nvarchar(255)), '') as EmpSec,
      
                    isnull(cast(EmpTyp collate database_default as nvarchar(255)), '') as EmpTyp,
      
                    isnull(cast(Postcode collate database_default as nvarchar(255)), '') as Postcode,
      
                    isnull(cast(State collate database_default as nvarchar(255)), '') as State,
      
                    isnull(cast(Country collate database_default as nvarchar(255)), '') as Country,
      
                    row_number() over (partition by idno order by cast(FQ_CRE_TMS as datetime) desc) as RowNum
      
            from #mytable
      
        ) unpivot_table
      
    unpivot
      
        (
      
            vals for colname in (Name, DOB, BasicGroup, Nationality, CorporateStatus, 
      
            IndustrialSector, ResidencyStatus, Gender, Income, Occupation, EmpName, EmpSec,
      
            EmpTyp, Postcode, State, Country)
      
        ) unpivot_handle
      
)
      
, add_column_name AS
      
(
      
    select reqid, IDNo, colname,[2] as [From_Value], [1] as [To_Value]
      
    FROM 
      
    (
      
        select reqid, IDNo, RowNum, colname, vals
      
        from #unpivot_step2
      
    ) pivot_table
      
    pivot
      
    (
      
        max (vals) for RowNum in ([1],[2])
      
    ) pivot_handle
      
    where [1] <> [2]
      
)
      

      
select distinct p.*, m.Name
      
into #step1
      
from add_column_name as p
      
inner join  #mytable as m
      
on p.reqid = m.Reqid
      

      
select * from #mytable
      
select * from #step1


      This is the result I got after execute the script from above



      reqid|IDNo|colname|From_Value|To_Value|Name
      
170916258|100202025698   |Gender|L|P|APPLICANT5_2
      
170916258|100202025698   |Gender|L|P|CUST3_6
      
170916258|100202025698   |Gender|L|P|CUST4_3
      
170916258|100202025698   |Gender|L|P|CUST5_3
      
170916258|100202025698   |Gender|L|P|CUTS4_3
      
170916258|100202025698   |Gender|L|P|EHH4_4
      
170916258|100202025698   |Gender|L|P|LALA
      
170916258|100202025698   |Gender|L|P|TEST2
      
170916258|100202025698   |Gender|L|P|TEST3_2
      
170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|APPLICANT5_2
      
170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|CUST3_6
      
170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|CUST4_3
      
170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|CUST5_3
      
170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|CUTS4_3
      
170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|EHH4_4
      
170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|LALA
      
170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|TEST2
      
170916258|100202025698   |Name|APPLICANT5_2|CUST3_6|TEST3_2
      
170916258|100202025698   |ResidencyStatus|L|F|APPLICANT5_2
      
170916258|100202025698   |ResidencyStatus|L|F|CUST3_6
      
170916258|100202025698   |ResidencyStatus|L|F|CUST4_3
      
170916258|100202025698   |ResidencyStatus|L|F|CUST5_3
      
170916258|100202025698   |ResidencyStatus|L|F|CUTS4_3
      
170916258|100202025698   |ResidencyStatus|L|F|EHH4_4
      
170916258|100202025698   |ResidencyStatus|L|F|LALA
      
170916258|100202025698   |ResidencyStatus|L|F|TEST2
      
170916258|100202025698   |ResidencyStatus|L|F|TEST3_2
      
170916258|260404045698   |Name|CUST4_3|EHH4_4|APPLICANT5_2
      
170916258|260404045698   |Name|CUST4_3|EHH4_4|CUST3_6
      
170916258|260404045698   |Name|CUST4_3|EHH4_4|CUST4_3
      
170916258|260404045698   |Name|CUST4_3|EHH4_4|CUST5_3
      
170916258|260404045698   |Name|CUST4_3|EHH4_4|CUTS4_3
      
170916258|260404045698   |Name|CUST4_3|EHH4_4|EHH4_4
      
170916258|260404045698   |Name|CUST4_3|EHH4_4|LALA
      
170916258|260404045698   |Name|CUST4_3|EHH4_4|TEST2
      
170916258|260404045698   |Name|CUST4_3|EHH4_4|TEST3_2
      
170916258|260404045698   |ResidencyStatus|L|S|APPLICANT5_2
      
170916258|260404045698   |ResidencyStatus|L|S|CUST3_6
      
170916258|260404045698   |ResidencyStatus|L|S|CUST4_3
      
170916258|260404045698   |ResidencyStatus|L|S|CUST5_3
      
170916258|260404045698   |ResidencyStatus|L|S|CUTS4_3
      
170916258|260404045698   |ResidencyStatus|L|S|EHH4_4
      
170916258|260404045698   |ResidencyStatus|L|S|LALA
      
170916258|260404045698   |ResidencyStatus|L|S|TEST2
      
170916258|260404045698   |ResidencyStatus|L|S|TEST3_2
      
170916258|50505050505   |Name|TEST2|TEST3_2|APPLICANT5_2
      
170916258|50505050505   |Name|TEST2|TEST3_2|CUST3_6
      
170916258|50505050505   |Name|TEST2|TEST3_2|CUST4_3
      
170916258|50505050505   |Name|TEST2|TEST3_2|CUST5_3
      
170916258|50505050505   |Name|TEST2|TEST3_2|CUTS4_3
      
170916258|50505050505   |Name|TEST2|TEST3_2|EHH4_4
      
170916258|50505050505   |Name|TEST2|TEST3_2|LALA
      
170916258|50505050505   |Name|TEST2|TEST3_2|TEST2
      
170916258|50505050505   |Name|TEST2|TEST3_2|TEST3_2


      ‌‌



      H‌ow can I get as below result?



      +-----------+--------------+------------------+--------------+----------+---------+------------+----------+
      
|   reqid   |     IDNo     |     colname      |  From_Value  | To_Value |  Name   | BasicGroup | Postcode |
      
+-----------+--------------+------------------+--------------+----------+---------+------------+----------+
      
| 170916258 | 100202025698 | Gender           | L            | P        | CUST3_6 | NULL       |    60000 |
      
| 170916258 | 100202025698 | Name             | APPLICANT5_2 | CUST3_6  | CUST3_6 | NULL       |    60000 |
      
| 170916258 | 100202025698 | ResidencyStatus  | L            | P        | CUST3_6 | NULL       |    60000 |
      
| 170916258 | 100202025698 | IndustrialSector | 1261         | NULL     | CUST3_6 | NULL       |    60000 |
      
+-----------+--------------+------------------+--------------+----------+---------+------------+----------+


      colname, From_Value, To_Value is based from rowNum = 1 , 2 from #mytable ( want to get any result that got changes)




      1. Name, BasicGroup, Postcode and other column (gender, DOB, income)[in the result I want] is to find the most update that got result. EG: ID=100202025698, postcode only available in rownum=3 in #mytable and I want show it out.


      A‌ny idea how to enhance my script? I want it generally working for every table not for this special case.






      sql-server




      sql-server






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked 12 hours ago









      user3542587user3542587

      13




      13




      migrated from serverfault.com 10 hours ago


      This question came from our site for system and network administrators.









      migrated from serverfault.com 10 hours ago


      This question came from our site for system and network administrators.


























          0






          active

          oldest

          votes











          Your Answer






          StackExchange.ifUsing("editor", function () {
          StackExchange.using("externalEditor", function () {
          StackExchange.using("snippets", function () {
          StackExchange.snippets.init();
          });
          });
          }, "code-snippets");

          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "1"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          autoActivateHeartbeat: false,
          convertImagesToLinks: true,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: 10,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });














          draft saved

          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54970549%2fcompare-to-rows-and-find-what-columns-changed%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          0






          active

          oldest

          votes








          0






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes
















          draft saved

          draft discarded




















































          Thanks for contributing an answer to Stack Overflow!


          • Please be sure to answer the question. Provide details and share your research!

          But avoid …



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.




          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54970549%2fcompare-to-rows-and-find-what-columns-changed%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown





















































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown

































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown







          -

          Popular posts from this blog

          117736 Шеррод Примітки | Див. також | Посилання | Навігаційне...

          Image
          Астрономічні об'єкти, відкриті 2005Головний пояс астероїдів астероїдголовного поясу4 квітня2005Тіссеранів параметрЮпітера(117735) 2005 GQ20(117737) 2005 GX22 117736 Шеррод Відкриття Відкривач Мішель Орі Місце відкриття Астрономічна обсерваторія Юра Дата відкриття 4 квітня 2005 Позначення Тимчасові назви 2005 GQ22 2003 XZ38 Категорія малої планети Астероїд головного поясу Орбітальні характеристики [1] Епоха 23 травня 2014 (2 456 800,5 JD) Велика піввісь 2,705725184814 а. о. Перигелій 2,448701683278 а. о. Афелій 2,962748686350 а. о. Ексцентриситет 0,094992463750 Орбітальний період 1625,638413326 д Середня орбітальна швидкість 0,221451459961 °/д Середня аномалія 55,04924415012° Нахил орбіти 16,61417836034° Довгота висхідного вузла 36,21254615972° Аргумент перицентру 130,7000680959° Фізичні характеристики Стандартна зоряна вел...
          Read more

          As a Security Precaution, the user account has been locked The Next CEO of Stack OverflowMS...

          Image
          Could a dragon use its wings to swim? Is it a bad idea to plug the other end of ESD strap to wall ground? How can I prove that a state of equilibrium is unstable? Compilation of a 2d array and a 1d array pgfplots: How to draw a tangent graph below two others? How dangerous is XSS Gauss' Posthumous Publications? How to implement Comparable so it is consistent with identity-equality What does this strange code stamp on my passport mean? "Eavesdropping" vs "Listen in on" Which acid/base does a strong base/acid react when added to a buffer solution? ...
          Read more

          Маріан Котлеба Зміст Життєпис | Політичні погляди |...

          Image
          Народились 7 квітняНародились 1977Уродженці Банської БистриціВипускники Університету Матея БелаСловацькі політикиРевізіоністи ГолокостуРомофобиУльтраправіШовіністиПопулізмКритики Європейського союзуАнтиамериканізм словац.7 квітня1977Банська БистрицяЧССРультраправийсловацькийполітикЙозефу ТісоПершій Словацькій республіціВолодимиру ПутінуциганНАТОСШАЄвросоюзусловацьке національне повстанняантисемітськоїпопулістської«Котлеба — Народної партії Наша Словаччина»Банськобистрицького краю7 квітня1977Банська БистрицяЧССРуніверситету Матея Бела20062000200921 лютого2010єврословацьку кронугромадського партнерстваЄСНАТОромської меншини2012напіввоєнізованих структурМіністерство внутрішніх справчервні2016Верховний судБанськобистрицькому краї2016однопалатний словацький законодавчий орган«Котлеба — Народна партія Наша Словаччина»Банськобистрицького краю«Глінкова гвардія»Другої світової війни«Революцію гідності» Маріан Котлеба словац. Marian Kotleba Народився 7 квітня 1977 ( 1977-04-...
          Read more