如何将列转换为行? [英] How to convert columns to rows?

问题描述

我有一张这样的桌子

RowNum | TranNo | nTotalSales | nBalance
   1   |    1   |    800      |    0

我想这样显示

RowNum      |   1
cTranNo     |   1
nTotalSales |  800
nBalance    |   0

我该怎么做?

推荐答案

这是一个完整的工作示例，当您执行 UNPIVOT 时，这就是您所要求的，您的价值"类型需要是相同的类型，所以你可以随意转换它们.在我的示例中，我已将它们全部转换为 VARCHAR(20):

Here is a complete working example, when you you do an UNPIVOT, which is what your are asking for, your 'value' types need to be of the same type, so cast them however you want. In my example, I have cast them all to VARCHAR(20):

DECLARE @bob TABLE
(
    RowNum INT,
    TranNo INT,
    nTotalSales INT,
    nBalance INT
);
INSERT INTO @bob(RowNum, TranNo, nTotalSales, nBalance)
VALUES(1, 1, 800, 0);


WITH T AS (
    SELECT CAST(RowNum      AS VARCHAR(20)) AS RowNum,
           CAST(TranNo      AS VARCHAR(20)) AS TranNo,
           CAST(nTotalSales AS VARCHAR(20)) AS nTotalSales,
           CAST(nBalance    AS VARCHAR(20)) AS nBalance
    FROM @bob
)

SELECT attribute, value
FROM T
UNPIVOT(value FOR attribute IN(RowNum, TranNo, nTotalSales, nBalance)) AS U;

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