There are several ways that you can transform data from multiple rows into columns. In SQL Server you can use the
PIVOT function to transform the data from rows to columns:
select Firstname, Amount, PostalCode, LastName, AccountNumber
select value, columnname
for columnname in (Firstname, Amount, PostalCode, LastName, AccountNumber)
If you have an unknown number of
columnnames that you want to transpose, then you can use dynamic SQL:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(ColumnName)
group by ColumnName, id
order by id
FOR XML PATH(''), TYPE
set @query = N'SELECT ' + @cols + N' from
select value, ColumnName
for ColumnName in (' + @cols + N')
) p '
exec sp_executesql @query;
If you do not want to use the
PIVOT function, then you can use an aggregate function with a
max(case when columnname = 'FirstName' then value end) Firstname,
max(case when columnname = 'Amount' then value end) Amount,
max(case when columnname = 'PostalCode' then value end) PostalCode,
max(case when columnname = 'LastName' then value end) LastName,
max(case when columnname = 'AccountNumber' then value end) AccountNumber
This could also be completed using multiple joins, but you will need some column to associate each of the rows which you do not have in your sample data. But the basic syntax would be:
select fn.value as FirstName,
a.value as Amount,
pc.value as PostalCode,
ln.value as LastName,
an.value as AccountNumber
from yourtable fn
left join yourtable a
on fn.somecol = a.somecol
and a.columnname = 'Amount'
left join yourtable pc
on fn.somecol = pc.somecol
and pc.columnname = 'PostalCode'
left join yourtable ln
on fn.somecol = ln.somecol
and ln.columnname = 'LastName'
left join yourtable an
on fn.somecol = an.somecol
and an.columnname = 'AccountNumber'
where fn.columnname = 'Firstname'