Multiple UNPIVOT in SQL Server

Learning multiple unpivot option was little interesting for me.

SELECT tempoid,version,dataField,datavalue,dataField1,datavalue1

FROM

      (SELECT t1.tempoid, t1.version, t1.data1, t1.data2, t1.data3, t1.data4, t2.data1 as d1, t2.data2 as d2, t2.data3 as d3, t2.data4 as d4

      FROM table1 t1 inner join table1 t2 on t1.tempoid = t1.tempoid and t1.version = t2.version + 1

      WHERE t1.version in (@version)

      ) AS p

UNPIVOT

      (datavalue FOR dataField IN

            (data1, data2, data3, data4)) as u1

 UNPIVOT (datavalue1 FOR dataField1 IN

      (d1, d2, d3, d4)) as u2

That’s it, simple

To under the above query, we will first create two tables and insert few records into

create table table1(OID BIGINT identity,tempoid bigint,version varchar(30),data1 varchar(30),data2 varchar(30),data3 varchar(30),data4 varchar(30))

create table table2(oid bigint identity,tempoid bigint,version varchar(30),fieldname varchar(30),Olddata varchar(30),Newdata varchar(30))

select * from table1

select * from table2

Let us first try inserting through this procedure

CREATE proc procInsert(@tempoid bigint,@version varchar(30),@data1 varchar(30),@data2 varchar(30),@data3 varchar(30),@data4 varchar(30))

AS

BEGIN

INSERT INTO table1 values(@tempoid,@version,@data1,@data2,@data3,@data4)

IF(@version >1)

BEGIN

;with cte as(

select tempoid,version,dataField,datavalue,dataField1,datavalue1

FROM

(SELECT t1.tempoid, t1.version, t1.data1, t1.data2, t1.data3, t1.data4, t2.data1 as d1, t2.data2 as d2, t2.data3 as d3, t2.data4 as d4

FROM table1 t1 inner join table1 t2 on t1.tempoid = t1.tempoid and t1.version = t2.version + 1

WHERE t1.version in (@version)

) AS p

UNPIVOT (datavalue FOR dataField IN (data1, data2, data3, data4)) as u1

UNPIVOT (datavalue1 FOR dataField1 IN (d1, d2, d3, d4)) as u2

)

–select * from cte

Insert into table2

select cte.tempoid,cte.version,cte.datafield,cte.datavalue1  as NewValue,cte.datavalue as OldValue

from cte inner join table1 t1 on t1.tempoid = cte.tempoid and t1.version = cte.version where ((cte.datafield = ‘data1’ and cte.datafield1 = ‘d1’)

or (cte.datafield = ‘data2’ and cte.datafield1 = ‘d2’) or (cte.datafield = ‘data3’ and cte.datafield1 = ‘d3’) or (cte.datafield = ‘data4’ and cte.datafield1 = ‘d4’))

AND cte.datavalue <> cte.datavalue1

END

END

go

procInsert 1,2,’2.1′,’2.2′,’2.3′,’2.4′

procInsert 1,3,’2.1′,’2.2′,’2.3′,’2.5′

Now select the records from both the tables

select * from table1

select * from table2

The required functionality is, the no of columns that are different from current row to its previous version

Those different columns should be recorded in a new table ‘table2’

now try inserting new row in table1 like

procInsert 1,4,’2.6′,’2.7′,’2.8′,’2.5′ — I have modified 3 columns here data1, data2 and data3

the result is :

(1 row(s) affected)

(3 row(s) affected)

and the output is

-Yuva