How to convert columns to rows?

Question

I have a table like this one

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

and I want to display it this way

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

How can I do this?

Answer 1

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;