如何在 SQL Server 中将多行转换为列? [英] How to convert many rows into Columns in SQL Server?

问题描述

如何将存储为多行的字段转换为列?我也列出了下面的代码.下面是所需内容的示例，但它实际上最多可以达到 20 列.谢谢！

How would you convert a field that is stored as multiple rows into columns? I listed the code below as well. Below is an example of what is needed but it can really go up to 20 columns. Thanks!

COL1  COL2  COL3
----------------
TEST  30    NY
TEST  30    CA
TEST2 10    TN 
TEST2 10    TX

我希望输出为:

COL1  COL2  COL3  COL4
------------------------
TEST  30    NY    CA
TEST2 10    TN    TX

select * from (
    select
    ID,
    Name,
    STORE,
    Group,
    Type,
    Date,
    State,

        row_number() over(partition by ID, state order by Date desc) as rn
    from
        #test
) t
where t.rn = 1

推荐答案

有多种选项可以将数据从行转换为列.在 SQL 中，您可以使用 PIVOT 将数据从行转换为列.

There are multiple options to convert data from rows into columns. In SQL, you can use PIVOT to transform data from rows into columns.

CREATE table #tablename
  (Id int, Value varchar(10), ColumnName varchar(15);


INSERT INTO #tablename
  (ID,  Value, ColumnName)

VALUES
  (1, ‘Lucy’, 'FirstName'),
  (2, ‘James’, ‘LastName’),
  (3, ‘ABCDXX’, ‘Adress’),
  (4, ’New York’, ‘City’),
  (5, '8572685', ‘PhoneNo’);

select FirstName, LastName, Address, City, PhoneNo
from
(
 select Value, ColumnName
 from #tablename
) d
pivot
(
 max(Value)
 for ColumnName in (FirstName, LastName, Address, City, PhoneNo)
) piv;

有关将数据从行转换为列的其他选项，请参阅以下链接:

Refer the below link for other options of transforming data from rows to columns:

https://www.sqlshack.com/多选项转行到列/

这篇关于如何在 SQL Server 中将多行转换为列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！