Compare Two Row in the table and find what columns are changed and take the latest resultPage Split...
QGIS: use geometry from different layer in symbology expression
What's the most convenient time of year in the USA to end the world?
Eww, those bytes are gross
Does the "particle exchange" operator have any validity?
A starship is travelling at 0.9c and collides with a small rock. Will it leave a clean hole through, or will more happen?
What is the purpose of easy combat scenarios that don't need resource expenditure?
The vanishing of sum of coefficients: symmetric polynomials
How did the original light saber work?
Am I a Rude Number?
What is the etymology of the kanji 食?
What formula could mimic the following curve?
What are the advantages of using `make` for small projects?
Manipulating a general length function
Why is button three on trumpet almost never used alone?
How does Internet communication work on a coaxial cable?
Do authors have to be politically correct in article-writing?
Can a person refuse a presidential pardon?
Is there hidden data in this .blend file? Trying to minimize the file size
page split between longtable caption and table
When does coming up with an idea constitute sufficient contribution for authorship?
Reference on complex cobordism
Jumping Numbers
Using loops to create tables
Program that converts a number to a letter of the alphabet
Compare Two Row in the table and find what columns are changed and take the latest result
Page Split TimingMake sure two columns are sorted in the same orderStored 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 filePartitioned Tables and Indexes - what are the downsides?How to compare and aggregate data in a tableCompare two tables and find dates present in one and missing in anotherCompare all 70 columns from two tables in same databaseFind out the number of rows/columns changed - SQL Server?
1
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?
sql-server sql-server-2012 sql-server-2014
asked 20 hours ago
user3542587user3542587
62
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.
add a comment |
1
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?
sql-server sql-server-2012 sql-server-2014
asked 20 hours ago
user3542587user3542587
62
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.
Hi, giving a reusable example would help people solve your question. Such as the table definition, some sample data and the entire query.
– Randi Vertongen
15 hours ago
@RandiVertongen updated as requested. hopefully you are able to help me and willing to spend some time for this. thanks
– user3542587
13 hours ago
add a comment |
1
1
1
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?
sql-server sql-server-2012 sql-server-2014
asked 20 hours ago
user3542587user3542587
62
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 #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?
sql-server sql-server-2012 sql-server-2014
sql-server sql-server-2012 sql-server-2014
asked 20 hours ago
user3542587user3542587
62
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 20 hours ago
user3542587user3542587
62
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.
edited 12 mins ago
user3542587
asked 20 hours ago
user3542587user3542587
62
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 20 hours ago
user3542587user3542587
62
asked 20 hours ago
user3542587user3542587
62
62
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.
Hi, giving a reusable example would help people solve your question. Such as the table definition, some sample data and the entire query.
– Randi Vertongen
15 hours ago
@RandiVertongen updated as requested. hopefully you are able to help me and willing to spend some time for this. thanks
– user3542587
13 hours ago
add a comment |
Hi, giving a reusable example would help people solve your question. Such as the table definition, some sample data and the entire query.
– Randi Vertongen
15 hours ago
@RandiVertongen updated as requested. hopefully you are able to help me and willing to spend some time for this. thanks
– user3542587
13 hours ago
Hi, giving a reusable example would help people solve your question. Such as the table definition, some sample data and the entire query.
– Randi Vertongen
15 hours ago
Hi, giving a reusable example would help people solve your question. Such as the table definition, some sample data and the entire query.
– Randi Vertongen
15 hours ago
@RandiVertongen updated as requested. hopefully you are able to help me and willing to spend some time for this. thanks
– user3542587
13 hours ago
@RandiVertongen updated as requested. hopefully you are able to help me and willing to spend some time for this. thanks
– user3542587
13 hours ago
add a comment |
1 Answer
1
active
oldest
votes
0
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;
answered 9 hours ago
SQLRaptorSQLRaptor
2,3761320
add a comment |
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f231114%2fcompare-two-row-in-the-table-and-find-what-columns-are-changed-and-take-the-late%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
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;
answered 9 hours ago
SQLRaptorSQLRaptor
2,3761320
add a comment |
0
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;
answered 9 hours ago
SQLRaptorSQLRaptor
2,3761320
add a comment |
0
0
0
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;
answered 9 hours ago
SQLRaptorSQLRaptor
2,3761320
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;
answered 9 hours ago
SQLRaptorSQLRaptor
2,3761320
edited 9 hours ago
answered 9 hours ago
SQLRaptorSQLRaptor
2,3761320
answered 9 hours ago
SQLRaptorSQLRaptor
2,3761320
answered 9 hours ago
SQLRaptorSQLRaptor
2,3761320
2,3761320
add a comment |
add a comment |
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f231114%2fcompare-two-row-in-the-table-and-find-what-columns-are-changed-and-take-the-late%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
Hi, giving a reusable example would help people solve your question. Such as the table definition, some sample data and the entire query.
– Randi Vertongen
15 hours ago
@RandiVertongen updated as requested. hopefully you are able to help me and willing to spend some time for this. thanks
– user3542587
13 hours ago