Given the following scenario:
create table vertical(
CustomerID int,
attribute varchar(255),
value varchar(255)
)
create table horizontal(
CustomerID int,
name varchar(255),
surname varchar(255),
phone varchar(255)
)
insert into vertical
select 30, 'Name', 'John'
insert into vertical
select 30, 'Surname', 'Smith'
insert into vertical
select 30, 'Phone', '551123456'
insert into vertical
select 40, 'Name', 'Mary'
insert into vertical
select 40, 'Surname', 'Johnson'
insert into vertical
select 40, 'Phone', '9991111234'
insert into vertical
select 50, 'Surname', 'Manuel'
insert into vertical
select 50, 'Phone', '423'
running this insert
insert into horizontal (CustomerID, name, surname, phone)
select c.CustomerID, n.value as name, s.value as surname, ph.value as phone
from
(select distinct CustomerID from vertical) as c
left join vertical n
on c.CustomerID = n.CustomerID and n.attribute = 'name'
left join vertical s
on c.CustomerID = s.CustomerID and s.attribute = 'Surname'
left join vertical ph
on c.CustomerID = ph.CustomerID and ph.attribute = 'Phone'
shall output this:
ID Name Surname Phone
-- ---- ------- -----
30 John Smith 551123456
40 Mary Johnson 9991111234
50 NULL Manuel 423
Where:
from: select distinct CustomerID from vertical
brings all customers that have at least one attribute.
each left join returns the corresponding value for each attribute.
left join was used to ensure that the customer row will be inserted even if the customer is missing some attributes in which case NULL will be inserted.
source table named vertical is the one with the attribute values
target table named horizontal is your desired output