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

          ORA-01691 (unable to extend lob segment) even though my tablespace has AUTOEXTEND onORA-01692: unable to...

          Image
          Do I need to convey a moral for each of my blog post? Does convergence of polynomials imply that of its coefficients? Are hand made posters acceptable in Academia? How to read string as hex number in bash? Have the tides ever turned twice on any open problem? How do researchers send unsolicited emails asking for feedback on their works? How to determine the greatest d orbital splitting? When did hardware antialiasing start being available? How to test the sharpness of a knife? Asserting that Atheism and Theism are both faith based positions Print a physical multiplication table
          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

          Circunscripción electoral de Guipúzcoa Referencias Menú de navegaciónLas claves del sistema electoral en...

          Image
          Circunscripciones electorales del Senado de EspañaCircunscripciones electorales del Congreso de los DiputadosCircunscripciones electorales del Parlamento Vasco españolasCongreso de los DiputadosSenadobajaaltaPaís Vascoelecciones autonómicasprovincia homónima Localización de la circunscripción electoral de Guipúzcoa. Guipúzcoa es una de las 52 circunscripciones electorales españolas utilizadas como distritos electorales para el Congreso de los Diputados y el Senado, que son las cámaras baja y alta del Parlamento Español. [ 1 ] ​ Le corresponden 6 diputados y 4 senadores. También es una de las 3 circunscripciones electorales del País Vasco para las elecciones autonómicas, en que elige 25 parlamentarios. Se corresponde con la provincia homónima. [ 2 ] ​ Referencias ↑ Las claves del sistema electoral en España ↑ Resultados de las elecciones autonómicas de 2012 en Guipúzcoa This page is only for reference, If you need detailed information, please check here
          Read more