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', '' UNION ALL
SELECT 2, 'FirstName', 'Jack' UNION ALL
SELECT 2, 'LastName', 'Daniels' UNION ALL
SELECT 2, 'Email', ''

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
4 2 FirstName Jack
5 2 LastName Daniels
6 2 Email


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

SELECT [Id], [FirstName], [LastName], [Email] 
 SELECT Id, Att_Id, Att_Value FROM VerticalTable
) as source
 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
2 2 Jack Daniels


You can find the code here.



Please enter your comment!
Please enter your name here