How Can I Efficiently Convert Rows to Columns in SQL Server?

Efficient row to column conversion method in SQL Server

In SQL Server, there are multiple ways to convert rows into columns.

Use the PIVOT function

The PIVOT function can transpose data from rows to columns. Here is an example query using PIVOT:

<code class="language-sql">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;</code>

This query will output the following results:

FirstName	Amount	PostalCode	LastName	AccountNumber
John	2.4	ZH1E4A	Fork	857685

Please note that the PIVOT function may be less efficient for large data sets.

Use aggregate functions and CASE expressions

If PIVOT is not applicable, you can use aggregate functions and CASE expressions, as shown below:

<code class="language-sql">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</code>

This query will produce the same results as the PIVOT query.

Use multiple JOIN connections

Another approach is to use multiple JOIN connections, as shown below:

<code class="language-sql">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'</code>

Please note that using multiple JOIN connections may not be efficient for large data sets. Which method you choose depends on your specific data and performance requirements.

The above is the detailed content of How Can I Efficiently Convert Rows to Columns in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!