FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

create table history (
  id int,
  table_name nvarchar(100),
  record_id int,
  column_name nvarchar(100),
  old_value nvarchar(100),
  created_by_id int,
  created_on datetime
)

create table employees (
  id int,
  name nvarchar(100),
  address nvarchar(100),
  telephone_number nvarchar(100),
  comments nvarchar(100),
  created_by_id int,
  created_on datetime,
  modified_on datetime
)

insert into employees (
  id,
  name,
  address,
  telephone_number,
  comments,
  created_by_id,
  created_on,
  modified_on
) values (
  '1',
  'Joe Bloggs',
  '123 Fake Street',
  '+81123123',
  null,
  1,
  '2012-06-01 18:56:34.123',
  '2018-02-12 12:59:05.531'
)

insert into history values (
  1,
  'employees',
  1,
  'name',
  'Joe Smith',
  2,
  '2013-12-01 12:39:01.120'
), (
  2,
  'employees',
  1,
  'address',
  '124 Fake Street',
  3,
  '2014-07-19 21:23:01.142'
), (
  3,
  'employees',
  1,
  'comments',
  'Best employee ever',
  1,
  '2015-08-23 21:20:01.876'
), (
  4,
  'employees',
  1,
  'address',
  '125 Fake Street',
  3,
  '2016-10-20 07:23:01.583'
), (
  5,
  'employees',
  1,
  'comments',
  'He sure does move alot',
  3,
  '2017-03-12 23:59:56.456'
), (
  6,
  'employees',
  1,
  'comments',
  'He ran out of money to move and we ain''t paying any more',
  3,
  '2018-02-12 12:59:05.530'
)

select *
from employees

select *
from history

----

select name, address, telephone_number, comments, 'current' as 'record_status', modified_on
from employees

union

select h.old_value, '', '', '', 'old', h.created_on
from employees e
inner join history h on h.record_id = e.id and h.table_name = 'employees'
where column_name = 'name'

union

select '', h.old_value, '', '', 'old', h.created_on
from employees e
inner join history h on h.record_id = e.id and h.table_name = 'employees'
where column_name = 'address'

union

select '', '', h.old_value, '', 'old', h.created_on
from employees e
inner join history h on h.record_id = e.id and h.table_name = 'employees'
where column_name = 'telephone_number'

union

select '', '', '', h.old_value, 'old', h.created_on
from employees e
inner join history h on h.record_id = e.id and h.table_name = 'employees'
where column_name = 'comments'
order by 5 asc, 6 desc

id	name	address	telephone_number	comments	created_by_id	created_on	modified_on
1	Joe Bloggs	123 Fake Street	+81123123	NULL	1	2012-06-01 18:56:34.123	2018-02-12 12:59:05.530

id	table_name	record_id	column_name	old_value	created_by_id	created_on
1	employees	1	name	Joe Smith	2	2013-12-01 12:39:01.120
2	employees	1	address	124 Fake Street	3	2014-07-19 21:23:01.143
3	employees	1	comments	Best employee ever	1	2015-08-23 21:20:01.877
4	employees	1	address	125 Fake Street	3	2016-10-20 07:23:01.583
5	employees	1	comments	He sure does move alot	3	2017-03-12 23:59:56.457
6	employees	1	comments	He ran out of money to move and we ain't paying any more	3	2018-02-12 12:59:05.530

name	address	telephone_number	comments	record_status	modified_on
Joe Bloggs	123 Fake Street	+81123123	NULL	current	2018-02-12 12:59:05.530
			He ran out of money to move and we ain't paying any more	old	2018-02-12 12:59:05.530
			He sure does move alot	old	2017-03-12 23:59:56.457
	125 Fake Street			old	2016-10-20 07:23:01.583
			Best employee ever	old	2015-08-23 21:20:01.877
	124 Fake Street			old	2014-07-19 21:23:01.143
Joe Smith				old	2013-12-01 12:39:01.120

(1 rows affected)

(6 rows affected)

(1 rows affected)

(6 rows affected)

(7 rows affected)