How to compare two rows and which columns and data got changed

See more:

SQL

SQL-Server

SQL-Server-2008R2

Hi Guys,

I need some help, I need to compare using my audit log on which column has changed and its old and new data. So I should have Changed_Column, Changed_From, Changed_To

CREATE TABLE #Sample_Ext_Upl_Hdr_HIST (
	[recid] [varchar](50) NULL,
	[upld_type] [varchar](50) NULL,
	[batch_id] [varchar](50) NULL,
	[season_id] [varchar](50) NULL,
	[T0] [varchar](50) NULL,
	[BU_Team] [varchar](50) NULL,
	[Dev_Team] [varchar](50) NULL,
	[remarks] [nvarchar](200) NULL,
	[created_by] [varchar](50) NULL,
	[create_dt] [datetime] NULL,
	[updated_by] [varchar](50) NULL,
	[update_dt] [datetime] NULL,
	[Audit_Type] [varchar](50) NULL,
	[Audit_Dt] [datetime] NULL
)
GO
INSERT INTO #Sample_Ext_Upl_Hdr_HIST  VALUES (N'6fd78922-f63e-450e-8b39-7b945915e4a2', N'Sample Style Header', N'SMP-81520182', N'2019-SS', N'ADI',  N'BAS', N'TEAM 35', N'Testing Only', N'chimcham', CAST(N'2018-08-15T20:38:47.490' AS DateTime), N'chimcham', CAST(N'2018-08-29T17:31:17.613' AS DateTime), N'UPDATE-OLD', CAST(N'2018-08-30T01:02:32.203' AS DateTime))
INSERT INTO #Sample_Ext_Upl_Hdr_HIST  VALUES (N'6fd78922-f63e-450e-8b39-7b945915e4a2', N'Sample Style Header', N'SMP-81520182', N'2018-SS', N'ADI',  N'BAS', N'TEAM 35', N'Testing Only', N'chimcham', CAST(N'2018-08-15T20:38:47.490' AS DateTime), N'chimcham', CAST(N'2018-08-30T01:02:01.980' AS DateTime), N'UPDATE-NEW', CAST(N'2018-08-30T01:02:32.203' AS DateTime))

Output Should be:

upld_type batch_id	season_id  T0	BU_Team	changed_column	changed_from	changed_to
Sample Style Header	SMP-81520182	2018-SS	ADI	BAS	season_id	2019-SS	2018-SS

Please help me how to..

What I have tried:

This is what I have tried so far.. (only 2 columns checking yet..) But I need to achieve the columns :

Changed_Column, Changed_From, Changed_To

SELECT 
   T1.RECID,
   T1.BATCH_ID,
   CASE 
     WHEN T1.SEASON_ID = T2.SEASON_ID 
     THEN T1.SEASON_ID 
     ELSE 'OLD:' + CAST(T1.SEASON_ID AS VARCHAR(20)) + ', NEW:' + CAST(T2.SEASON_ID AS VARCHAR(20)) 
     END AS SEASON_ID,
   CASE 
     WHEN T1.BU_TEAM = T2.BU_TEAM 
     THEN T1.BU_TEAM
     ELSE 'OLD:' + CAST(T1.BU_TEAM AS VARCHAR(20)) + ', NEW:' + CAST(T2.BU_TEAM AS VARCHAR(20)) 
     END AS BU_TEAM
FROM #SAMPLE_EXT_UPL_HDR_HIST T1
INNER JOIN #SAMPLE_EXT_UPL_HDR_HIST T2
   ON T1.RECID=T2.RECID 
   AND T1.BATCH_ID=T2.BATCH_ID 
   AND T1.AUDIT_TYPE='UPDATE-OLD' 
   AND T2.AUDIT_TYPE='UPDATE-NEW'
WHERE   
(
   T1.SEASON_ID <> T2.SEASON_ID 
   OR T1.BU_TEAM <> T2.BU_TEAM 
)