Xrhrft

create table #mydatabase

(

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

    [Reqid] [int] NOT NULL,

    [Name] [varchar](150) NULL,

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

    [EmpName] [varchar](150) NULL,

    [EmpTyp] [char](3) NULL,

    [Gender] [char](1) NULL,

    [Income] [char](20) NULL,

    [FQ_CRE_TMS] [datetime2] NULL,

)



set identity_insert #mydatabase ON



INSERT INTO #mydatabase (Id, reqid, Name, idno, EmpName, EmpTyp, Gender, Income, FQ_CRE_TMS) 

VALUES (14443, 170916258, 'CUTS4_3', 260404045698, 'REWQREREW', NULL, NULL, NULL, '2019-02-28 15:06:23.213');

INSERT INTO #mydatabase(Id,reqid,Name,idno,EmpName,EmpTyp,Gender,Income,FQ_CRE_TMS) VALUES (14444,170916258,'CUST5_3',100202025698,NULL,NULL,'L',NULL,'2019-02-28 15:07:01.790');

INSERT INTO #mydatabase(Id,reqid,Name,idno,EmpName,EmpTyp,Gender,Income,FQ_CRE_TMS) VALUES (14445,170916258,'CUST4_3',260404045698,NULL,NULL,NULL,NULL,'2019-02-28 15:08:24.090');

INSERT INTO #mydatabase(Id,reqid,Name,idno,EmpName,EmpTyp,Gender,Income,FQ_CRE_TMS) VALUES (14446,170916258,'CUST5_3',100202025698,NULL,NULL,NULL,NULL,'2019-02-28 15:08:49.650');

INSERT INTO #mydatabase(Id,reqid,Name,idno,EmpName,EmpTyp,Gender,Income,FQ_CRE_TMS) VALUES (14447,170916258,'CUST4_3',260404045698,NULL,NULL,NULL,NULL,'2019-02-28 15:13:14.670');

INSERT INTO #mydatabase(Id,reqid,Name,idno,EmpName,EmpTyp,Gender,Income,FQ_CRE_TMS) VALUES (14448,170916258,'CUST4_3',260404045698,NULL,NULL,NULL,NULL,'2019-02-28 15:14:35.380');

INSERT INTO #mydatabase(Id,reqid,Name,idno,EmpName,EmpTyp,Gender,Income,FQ_CRE_TMS) VALUES (14483,170916258,'LALA',100101015698,NULL,NULL,'L',NULL,'2019-02-28 21:16:42.293');

INSERT INTO #mydatabase(Id,reqid,Name,idno,EmpName,EmpTyp,Gender,Income,FQ_CRE_TMS) VALUES (14484,170916258,'APPLICANT5_2',100202025698,NULL,NULL,'L',NULL,'2019-02-28 21:17:24.420');

INSERT INTO #mydatabase(Id,reqid,Name,idno,EmpName,EmpTyp,Gender,Income,FQ_CRE_TMS) VALUES (14485,170916258,'CUST3_6',100202025698,NULL,NULL,'P',NULL,'2019-02-28 21:25:45.800');

INSERT INTO #mydatabase(Id,reqid,Name,idno,EmpName,EmpTyp,Gender,Income,FQ_CRE_TMS) VALUES (14486,170916258,'EHH4_4',260404045698,NULL,NULL,'P',NULL,'2019-02-28 21:26:28.967');

INSERT INTO #mydatabase(Id,reqid,Name,idno,EmpName,EmpTyp,Gender,Income,FQ_CRE_TMS) VALUES (14487,170916258,'TEST2',050505050505,NULL,NULL,'P',8891126,'2019-02-28 21:29:56.857');

INSERT INTO #mydatabase(Id,reqid,Name,idno,EmpName,EmpTyp,Gender,Income,FQ_CRE_TMS) VALUES (14488,170916258,'TEST2',780909096398,'YULU',NULL,NULL,NULL,'2019-02-28 21:31:46.340');

INSERT INTO #mydatabase(Id,reqid,Name,idno,EmpName,EmpTyp,Gender,Income,FQ_CRE_TMS) VALUES (14489,170916258,'TEST3_2',050505050505,'YOLO',113,NULL,NULL,'2019-02-28 21:32:48.247');

I used row_number() over (partition by idno order by cast(FQ_CRE_TMS as datetime) desc) as RowNum and unpivot/pivot trick.

;with #unpivot_step2 as

(

    select reqid, Idno, RowNum, ColName, Vals

    From 

        (

            select  

                    cast(reqid as varchar(150)) AS reqid,

                    cast(name collate database_default as varchar (150)) as name,

                    cast(idno collate database_default as varchar (150)) as idno,

                    cast(gender collate database_default as varchar (150)) as gender,

                    cast(income collate database_default as varchar (150)) as income,

                    cast(empname collate database_default as varchar(150)) as empname,

                    cast(emptyp collate database_default as varchar (150)) as emptyp,



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

            from #mydatabase

        ) unpivot_table

    unpivot

        (

            vals for colname in (name, gender, income, empname, emptyp)

        ) unpivot_handle

)



--Step 3: Pivot the data. Display it to Item, [From], [To]

--drop table #pivot_step3

--select * from #pivot_step3

select reqid, idno, colName,[2] as [From_Value], [1] as [To_Value]

--into #pivot_step3

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]

The result I got from the trick is 90% what I want but dont have Name Column.

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

|   reqid   |     idno     |  colName |  From_Value  | To_Value |

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

| 170916258 | 50505050505  | empname  |              | YOLO     |

| 170916258 | 50505050505  | emptyp   |              | 113      |

| 170916258 | 50505050505  | gender   | P            |          |

| 170916258 | 50505050505  | income   | 8891126      |          |

| 170916258 | 50505050505  | name     | TEST2        | TEST3_2  |

| 170916258 | 100202025698 | gender   | L            | P        |

| 170916258 | 100202025698 | name     | APPLICANT5_2 | CUST3_6  |

| 170916258 | 260404045698 | gender   |              | P        |

| 170916258 | 260404045698 | name     | CUST4_3      | EHH4_4   |

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

How can I get result like below:

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

|   reqid   |  Name   |     idno     |     colName      |  From_Value  | To_Value |

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

| 170916258 | TEST3_2 |  50505050505 | empname          |              | YOLO     |

| 170916258 | TEST3_2 |  50505050505 | emptyp           |              | 113      |

| 170916258 | TEST3_2 |  50505050505 | gender           | P            |          |

| 170916258 | TEST3_2 |  50505050505 | income           | 8891126      |          |

| 170916258 | TEST3_2 |  50505050505 | name             | TEST2        | TEST3_2  |

| 170916258 | EHH4_4  | 100202025698 | gender           | L            | P        |

| 170916258 | EHH4_4  | 100202025698 | name             | APPLICANT5_2 | CUST3_6  |

| 170916258 | EHH4_4  | 260404045698 | gender           |              | P        |

| 170916258 | EHH4_4  | 260404045698 | name             | CUST4_3      | EHH4_4   |

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

Any idea how to get the result that I want? Is there possible add Column_Name in unpivot/pivot trick? Any idea how to enhance my code?

Do you mean something like this?

;with #unpivot_step2 as

(

    select reqid, Idno, RowNum, ColName, Vals

    From 

        (

            select  

                    cast(reqid as varchar(150)) AS reqid,

                    cast(name collate database_default as varchar (150)) as name,

                    cast(idno collate database_default as varchar (150)) as idno,

                    cast(gender collate database_default as varchar (150)) as gender,

                    cast(income collate database_default as varchar (150)) as income,

                    cast(empname collate database_default as varchar(150)) as empname,

                    cast(emptyp collate database_default as varchar (150)) as emptyp,



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

            from #mydatabase

        ) unpivot_table

    unpivot

        (

            vals for colname in (name, gender, income, empname, emptyp)

        ) unpivot_handle

)

, NinetyPercent_Solution AS

(

--Step 3: Pivot the data. Display it to Item, [From], [To]

--drop table #pivot_step3

--select * from #pivot_step3

select reqid, idno, colName,[2] as [From_Value], [1] as [To_Value]

--into #pivot_step3

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

FROM NinetyPercent_Solution AS P

INNER JOIN #mydatabase AS M

ON P.reqid = M.reqid;