Converting a Vertical Table to an Horizontal Table in SQL Server

Today I’ve encountered a vertical table in an SQL Database and I wanted to transform it to an horizontal one. A vertical table is described as an EAV model.

Imagine you have this table


CREATE TABLE VerticalTable
(
Id int,
Att_Id varchar(50),
Att_Value varchar(50)
)

INSERT INTO VerticalTable
SELECT 1, 'FirstName', 'John' UNION ALL
SELECT 1, 'LastName', 'Smith' UNION ALL
SELECT 1, 'Email', 'john.smith@dummy.com' UNION ALL
SELECT 2, 'FirstName', 'Jack' UNION ALL
SELECT 2, 'LastName', 'Daniels' UNION ALL
SELECT 2, 'Email', 'jack.daniels@dummy.com'

If you run


SELECT * FROM VerticalTable

you get this

Id Att_Id Att_Value
1 1 FirstName John
2 1 LastName Smith
3 1 Email john.smith@dummy.com
4 2 FirstName Jack
5 2 LastName Daniels
6 2 Email jack.daniels@dummy.com

 

To convert this into an horizontal table I’m going to use PIVOT.


SELECT [Id], [FirstName], [LastName], [Email] 
FROM
(
 SELECT Id, Att_Id, Att_Value FROM VerticalTable
) as source
PIVOT
(
 MAX(Att_Value) FOR Att_Id IN ([FirstName], [LastName], [Email])
) as target

And I will get this

Id FirstName LastName Email
1 1 John Smith john.smith@dummy.com
2 2 Jack Daniels jack.daniels@dummy.com

 

You can find the code here.