Compare Two Row in the table and find what columns are changedMake sure two columns are sorted in the same...

Is there any differences between “gucken” and “schauen”?

Can a person refuse a presidential pardon?

Can we use the stored gravitational potential energy of a building to produce power?

Pendulum Rotation

A starship is travelling at 0.9c and collides with a small rock. Will it leave a clean hole through, or will more happen?

"On one hand" vs "on the one hand."

How did the original light saber work?

Is there a better way to make this?

What is the best way to simulate grief?

A flower in a hexagon

Dilemma of explaining to interviewer that he is the reason for declining second interview

Why zero tolerance on nudity in space?

What is better: yes / no radio, or simple checkbox?

Does Windows 10's telemetry include sending *.doc files if Word crashed?

Everyone is beautiful

Please help me understand the following solution

Can a hotel cancel a confirmed reservation?

The vanishing of sum of coefficients: symmetric polynomials

High pressure canisters of air as gun-less projectiles

Using loops to create tables

Called into a meeting and told we are being made redundant (laid off) and "not to share outside". Can I tell my partner?

How to deal with an incendiary email that was recalled

Explain the objections to these measures against human trafficking

If I delete my router's history can my ISP still provide it to my parents?



Compare Two Row in the table and find what columns are changed


Make sure two columns are sorted in the same orderOracle GoldenGate add trandata errorsStored procedure to compare two columns from different tables and make the insertWhat is the fastest way to compare polygon geometry data?Find out what columns are in a trace fileCompare two tables and find dates present in one and missing in anotherInvestigating errors from strange queryCompare all 70 columns from two tables in same databaseFind out the number of rows/columns changed - SQL Server?













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

) 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) 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');

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) 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');

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) 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');

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) 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');

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) 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');

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) 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');

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) 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');

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) 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');

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) 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');

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) 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');

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) 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');

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) 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');

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) 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');

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) 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');

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) 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');

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) 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');

‌

;with #unpivot_step2 as

(

    select reqid, Idno, RowNum, ColName, Vals

    From 

        (

            select    

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

                    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 order by IDNo

select * from #step1 order by IDNo‌‌‌


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 |

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


EDIT: Credit to SQLRaptor for enhance my script on my previous post.



1‌. How to get update NULL Value = 'whitespace' in unpivot/pivot case? the fastest way? I can think of update all column from NULL to 'whitespace' (I dont mind update 1 by 1 if i get desire result)




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


  2. Name, BasicGroup, Postcode and other column (gender, DOB, income) 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?






sql-server sql-server-2012






share







New contributor




user3542587 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

























    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
    
) 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) 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');
    
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) 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');
    
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) 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');
    
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) 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');
    
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) 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');
    
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) 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');
    
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) 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');
    
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) 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');
    
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) 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');
    
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) 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');
    
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) 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');
    
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) 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');
    
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) 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');
    
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) 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');
    
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) 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');
    
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) 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');
    
‌
    
;with #unpivot_step2 as
    
(
    
    select reqid, Idno, RowNum, ColName, Vals
    
    From 
    
        (
    
            select    
    
                    cast(reqid as nvarchar(255)) AS reqid,
    
                    cast(Name collate database_default as nvarchar(255)) as Name,
    
                    cast(IDNo collate database_default as nvarchar(255)) as IDNo,
    
                    cast(DOB collate database_default as nvarchar(255)) as DOB,
    
                    cast(BasicGroup collate database_default as nvarchar(255)) as BasicGroup,
    
                    cast(Nationality collate database_default as nvarchar(255)) as Nationality,
    
                    cast(CorporateStatus collate database_default as nvarchar(255)) as CorporateStatus,
    
                    cast(IndustrialSector collate database_default as nvarchar(255)) as IndustrialSector,
    
                    cast(ResidencyStatus collate database_default as nvarchar(255)) as ResidencyStatus,
    
                    cast(Gender collate database_default as nvarchar(255)) as Gender,
    
                    cast(Income collate database_default as nvarchar(255)) as Income,
    
                    cast(Occupation collate database_default as nvarchar(255)) as Occupation,
    
                    cast(EmpName collate database_default as nvarchar(255)) as EmpName,
    
                    cast(EmpSec collate database_default as nvarchar(255)) as EmpSec,
    
                    cast(EmpTyp collate database_default as nvarchar(255)) as EmpTyp,
    
                    cast(Postcode collate database_default as nvarchar(255)) as Postcode,
    
                    cast(State collate database_default as nvarchar(255)) as State,
    
                    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 order by IDNo
    
select * from #step1 order by IDNo‌‌‌


    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 |
    
+-----------+--------------+------------------+--------------+----------+---------+------------+----------+


    EDIT: Credit to SQLRaptor for enhance my script on my previous post.



    1‌. How to get update NULL Value = 'whitespace' in unpivot/pivot case? the fastest way? I can think of update all column from NULL to 'whitespace' (I dont mind update 1 by 1 if i get desire result)




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


    2. Name, BasicGroup, Postcode and other column (gender, DOB, income) 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?






    sql-server sql-server-2012






    share







    New contributor




    user3542587 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
    Check out our Code of Conduct.























      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
      
) 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) 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');
      
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) 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');
      
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) 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');
      
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) 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');
      
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) 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');
      
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) 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');
      
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) 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');
      
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) 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');
      
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) 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');
      
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) 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');
      
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) 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');
      
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) 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');
      
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) 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');
      
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) 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');
      
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) 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');
      
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) 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');
      
‌
      
;with #unpivot_step2 as
      
(
      
    select reqid, Idno, RowNum, ColName, Vals
      
    From 
      
        (
      
            select    
      
                    cast(reqid as nvarchar(255)) AS reqid,
      
                    cast(Name collate database_default as nvarchar(255)) as Name,
      
                    cast(IDNo collate database_default as nvarchar(255)) as IDNo,
      
                    cast(DOB collate database_default as nvarchar(255)) as DOB,
      
                    cast(BasicGroup collate database_default as nvarchar(255)) as BasicGroup,
      
                    cast(Nationality collate database_default as nvarchar(255)) as Nationality,
      
                    cast(CorporateStatus collate database_default as nvarchar(255)) as CorporateStatus,
      
                    cast(IndustrialSector collate database_default as nvarchar(255)) as IndustrialSector,
      
                    cast(ResidencyStatus collate database_default as nvarchar(255)) as ResidencyStatus,
      
                    cast(Gender collate database_default as nvarchar(255)) as Gender,
      
                    cast(Income collate database_default as nvarchar(255)) as Income,
      
                    cast(Occupation collate database_default as nvarchar(255)) as Occupation,
      
                    cast(EmpName collate database_default as nvarchar(255)) as EmpName,
      
                    cast(EmpSec collate database_default as nvarchar(255)) as EmpSec,
      
                    cast(EmpTyp collate database_default as nvarchar(255)) as EmpTyp,
      
                    cast(Postcode collate database_default as nvarchar(255)) as Postcode,
      
                    cast(State collate database_default as nvarchar(255)) as State,
      
                    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 order by IDNo
      
select * from #step1 order by IDNo‌‌‌


      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 |
      
+-----------+--------------+------------------+--------------+----------+---------+------------+----------+


      EDIT: Credit to SQLRaptor for enhance my script on my previous post.



      1‌. How to get update NULL Value = 'whitespace' in unpivot/pivot case? the fastest way? I can think of update all column from NULL to 'whitespace' (I dont mind update 1 by 1 if i get desire result)




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


      2. Name, BasicGroup, Postcode and other column (gender, DOB, income) 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?






      sql-server sql-server-2012






      share







      New contributor




      user3542587 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct. 












      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
      
) 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) 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');
      
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) 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');
      
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) 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');
      
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) 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');
      
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) 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');
      
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) 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');
      
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) 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');
      
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) 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');
      
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) 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');
      
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) 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');
      
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) 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');
      
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) 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');
      
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) 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');
      
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) 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');
      
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) 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');
      
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) 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');
      
‌
      
;with #unpivot_step2 as
      
(
      
    select reqid, Idno, RowNum, ColName, Vals
      
    From 
      
        (
      
            select    
      
                    cast(reqid as nvarchar(255)) AS reqid,
      
                    cast(Name collate database_default as nvarchar(255)) as Name,
      
                    cast(IDNo collate database_default as nvarchar(255)) as IDNo,
      
                    cast(DOB collate database_default as nvarchar(255)) as DOB,
      
                    cast(BasicGroup collate database_default as nvarchar(255)) as BasicGroup,
      
                    cast(Nationality collate database_default as nvarchar(255)) as Nationality,
      
                    cast(CorporateStatus collate database_default as nvarchar(255)) as CorporateStatus,
      
                    cast(IndustrialSector collate database_default as nvarchar(255)) as IndustrialSector,
      
                    cast(ResidencyStatus collate database_default as nvarchar(255)) as ResidencyStatus,
      
                    cast(Gender collate database_default as nvarchar(255)) as Gender,
      
                    cast(Income collate database_default as nvarchar(255)) as Income,
      
                    cast(Occupation collate database_default as nvarchar(255)) as Occupation,
      
                    cast(EmpName collate database_default as nvarchar(255)) as EmpName,
      
                    cast(EmpSec collate database_default as nvarchar(255)) as EmpSec,
      
                    cast(EmpTyp collate database_default as nvarchar(255)) as EmpTyp,
      
                    cast(Postcode collate database_default as nvarchar(255)) as Postcode,
      
                    cast(State collate database_default as nvarchar(255)) as State,
      
                    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 order by IDNo
      
select * from #step1 order by IDNo‌‌‌


      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 |
      
+-----------+--------------+------------------+--------------+----------+---------+------------+----------+


      EDIT: Credit to SQLRaptor for enhance my script on my previous post.



      1‌. How to get update NULL Value = 'whitespace' in unpivot/pivot case? the fastest way? I can think of update all column from NULL to 'whitespace' (I dont mind update 1 by 1 if i get desire result)




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


      2. Name, BasicGroup, Postcode and other column (gender, DOB, income) 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?






      sql-server sql-server-2012




      sql-server sql-server-2012





      share







      New contributor




      user3542587 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.










      share







      New contributor




      user3542587 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.








      share



      share






      New contributor




      user3542587 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.









      asked 2 mins ago









      user3542587user3542587

      12




      12




      New contributor




      user3542587 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.





      New contributor





      user3542587 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.






      user3542587 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.






















          0






          active

          oldest

          votes











          Your Answer








          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "182"
          };
          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: false,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: null,
          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
          });


          }
          });






          user3542587 is a new contributor. Be nice, and check out our Code of Conduct.










          draft saved

          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f231158%2fcompare-two-row-in-the-table-and-find-what-columns-are-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








          user3542587 is a new contributor. Be nice, and check out our Code of Conduct.










          draft saved

          draft discarded


















          user3542587 is a new contributor. Be nice, and check out our Code of Conduct.













          user3542587 is a new contributor. Be nice, and check out our Code of Conduct.












          user3542587 is a new contributor. Be nice, and check out our Code of Conduct.
















          Thanks for contributing an answer to Database Administrators Stack Exchange!


          • 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%2fdba.stackexchange.com%2fquestions%2f231158%2fcompare-two-row-in-the-table-and-find-what-columns-are-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

          Anexo:Material bélico de la Fuerza Aérea de Chile Índice Aeronaves Defensa...

          Image
          Fuerza Aérea de Chile materialFuerza Aérea de ChileJDAMAdvanced HawkTA-50Alenia Aermacchi M-346FLIRHydrahttp://www.infodefensa.com/latam/2014/02/26/noticia-fuerza-aerea-chile-interesada-a400m.htmlhttps://www.scramble.nl/index.php?option=com_mildb&view=search Aquí esta todo el material bélico de la Fuerza Aérea de Chile. Índice 1 Aeronaves 2 Defensa Antiaérea 3 Armamento 4 Material de reserva 5 Véase también 6 Referencias Aeronaves Aeronave Origen Tipo Versiones en servicio Total [ 1 ] ​ Fotos Aeronaves de la FACH (225) Lockheed Martin F-16 Estados Unidos   Estados Unidos   Países Bajos Caza Polivalente -AIM120/AAM Derby/JDAM/WCMD -Serán modernizados con radares AESA, <br>Se sumarán unidades adicionales 11A 7B 6C 4D 46 Northrop F-5E Tigre III Estados Unidos   Estados Unidos Caza Polivalente -Sistema de guerra electrónica mejorado -Equipado con el misil AAM Derby y casco Dash II
          Read more

          Always On Availability groups resolving state after failover - Remote harden of transaction...

          Image
          Should corporate security training be tailored based on a users' job role? Does human life have innate value over that of other animals? Limit involving inverse functions Father gets chickenpox, but doesn't infect his two children. How is this possible? Is it possible to re-order a List of continous entries? Who, if anyone, was the first astronaut to return to earth in a different vessel? The totem pole can be grouped into Identical projects by students at two different colleges: still plagiarism? Sci fi book, man buys a beat up spaceship and intervenes in a civil war on a planet and eventually becomes a space cop How to not
          Read more

          update json value to null Announcing the arrival of Valued Associate #679: Cesar Manara ...

          Image
          Seeking colloquialism for “just because” Sci-Fi book where patients in a coma ward all live in a subconscious world linked together Understanding Ceva's Theorem Dating a Former Employee How to find out what spells would be useless to a blind NPC spellcaster? Why am I getting the error "non-boolean type specified in a context where a condition is expected" for this request? Why are both D and D# fitting into my E minor key? Do I really need recursive chmod to restrict access to a folder? How to override model in magento2? How do I stop a creek from eroding my steep embankment? Wit
          Read more