Pivoting data in SqlServer

While working on transforming data (ETL) from a Entity-attribute-value data model, there are several challenges. Once of which is to transform data between values in a column in table and a string representation (like all values concatenated in a comma separated string), which I already described in one of my blog.

Another interesting challenge is to be able to transform attribute-values to a row structure, so that the attributes become column names, and you can see all the values for the attributes in a row.

I used the pivot function in SqlServer to achieve this as described below.

You need to know the attribute names to be able to lay them across as columns, but it is easy to generate sql string using my previous blog on concatenating strings for rows of a column.

CREATE TABLE #EAV_Test (

 ID int not null,

  code VARCHAR(30) NOT NULL,

  value VARCHAR(200),

  CONSTRAINT pk_EAV_Test

  PRIMARY KEY (ID, code));

INSERT INTO #EAV_Test (ID,code, value) VALUES(1,'Risk', 'High');

INSERT INTO #EAV_Test (ID,code, value) VALUES(1,'Priority', 'Med');

INSERT INTO #EAV_Test (ID,code, value) VALUES(1,'type', 'personal');

INSERT INTO #EAV_Test (ID,code, value) VALUES(1,'date', '20080215');

INSERT INTO #EAV_Test (ID,code, value) VALUES(1,'amount', '3500.00');

INSERT INTO #EAV_Test (ID,code, value) VALUES(2,'Risk', 'Low');

INSERT INTO #EAV_Test (ID,code, value) VALUES(2,'Priority', 'High');

INSERT INTO #EAV_Test (ID,code, value) VALUES(2,'type', 'open');

INSERT INTO #EAV_Test (ID,code, value) VALUES(2,'amount', '250.00');

SELECT ID, code, value FROM #EAV_Test;

select ID,Amount,[Date],[Priority],[Risk],[Type]  from

(

select Id, Code,Value from #EAV_Test) as t

  pivot (max(value) FOR code in (Amount,[Date],[Priority],[Risk],[Type])) as pt

  order by ID;

/* Results for previous query

ID     Amount Date   Priority      Risk   Type

1      3500.00       20080215      Med    High   personal

2      250.00 NULL   High   Low    open

*/

 

drop table #EAV_Test