sql - 在SQL Server中将行有效地转换为列(Efficiently convert rows to columns in sql server)

Question

I'm looking for an efficient way to convert rows to columns in SQL server, I heard that PIVOT is not very fast, and I need to deal with lot of records.

(我正在寻找一种在SQL Server中将行转换为列的有效方法，听说PIVOT速度不是很快，并且我需要处理很多记录。)

This is my example:

(这是我的示例：)

   -------------------------------
   | Id | Value  | ColumnName    |
   -------------------------------
   | 1  | John   | FirstName     |
   | 2  | 2.4    | Amount        |
   | 3  | ZH1E4A | PostalCode    |
   | 4  | Fork   | LastName      |
   | 5  | 857685 | AccountNumber |
   -------------------------------

This is my result:

(这是我的结果：)

---------------------------------------------------------------------
| FirstName  |Amount|   PostalCode   |   LastName  |  AccountNumber |
---------------------------------------------------------------------
| John       | 2.4  |   ZH1E4A       |   Fork      |  857685        |
---------------------------------------------------------------------

How can I build the result?

(如何建立结果？)

  ask by tbag translate from so

Answer 1

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

(您可以通过多种方式将数据从多行转换为列。)

Using PIVOT (使用PIVOT)

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

(在SQL Server中，可以使用PIVOT函数将数据从行转换为列：)

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 .

(参见演示 。)

Pivot with unknown number of columnnames (columnnames数量未知的数据透视)

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

(如果要转置的columnnames数量未知，则可以使用动态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 .

(参见演示 。)

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:

(如果您不想使用PIVOT函数，则可以将聚合函数与CASE表达式一起使用：)

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 .

(参见演示 。)

Using multiple joins (使用多个联接)

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'