Efficiently convert rows to columns

[ForumBot]

Efficiently convert rows to columns

[ForumBot]

There are several ways that you can transform data from multiple rows into columns.

Using `PIVOT`

In SQL Server you can use the `PIVOT` function to transform the data from rows to columns:

```
select Firstname, Amount, PostalCode, LastName, AccountNumber
from
(
select value, columnname
from yourtable
) d
pivot
(
max(value)
for columnname in (Firstname, Amount, PostalCode, LastName, AccountNumber)
) piv;

```

See [Demo](https://data.stackexchange.com/stackoverflow/query/497432).

Pivot with unknown number of `columnnames`

If you have an unknown number of `columnnames` that you want to transpose, then you must use dynamic SQL:

```
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(ColumnName)
from yourtable
group by ColumnName, id
order by id
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

set @query = N'SELECT ' + @cols + N' from
(
select value, ColumnName
from yourtable
) x
pivot
(
max(value)
for ColumnName in (' + @cols + N')
) p '

exec sp_executesql @query;

```

See [Demo](https://data.stackexchange.com/stackoverflow/query/497433).

Using an aggregate function

If you do not want to use the `PIVOT` function, then you can use an aggregate function with a `CASE` expression:

```
select
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
from yourtable

```

See [Demo](https://data.stackexchange.com/stackoverflow/query/497434).

Using multiple joins

This could also be completed us

*(Réponse tronquée)*