How To Convert Columns To Rows?

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 nT

Solution 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@bobTABLE
(
    RowNum INT,
    TranNo INT,
    nTotalSales INT,
    nBalance INT
);
INSERTINTO@bob(RowNum, TranNo, nTotalSales, nBalance)
VALUES(1, 1, 800, 0);


WITH T AS (
    SELECTCAST(RowNum      ASVARCHAR(20)) AS RowNum,
           CAST(TranNo      ASVARCHAR(20)) AS TranNo,
           CAST(nTotalSales ASVARCHAR(20)) AS nTotalSales,
           CAST(nBalance    ASVARCHAR(20)) AS nBalance
    FROM@bob
)

SELECT attribute, valueFROM T
UNPIVOT(valueFOR attribute IN(RowNum, TranNo, nTotalSales, nBalance)) AS U;

Solution 2:

SELECT'RowNum' TITLE, RowNum AS [VALUE]
FROMTABLEUNIONALLSELECT'TranNo', TranNo
FROMTABLEUNIONALLSELECT'nTotalSales', nTotalSales
FROMTABLEUNIONALLSELECT'nBalance', nBalance
FROMTABLE

Solution 3:

It's not real fun, but here's one solution:

SELECT'RowNum', RowNum FROM tbl
UNIONSELECT'cTranNo', TranNo FROM tbl
UNIONSELECT'nTotalSales', nTotalSales FROM tbl
UNIONSELECT'nBalance', nBalance FROM tbl

That will turn the columns into rows. If you want each of the column-rows to be interlaced, you may need to introduce a record number along with some sorting.

That would look like this:

SELECT'RowNum'AS ColName, RowNum AS [Value], RowNum FROM tbl
    UNIONSELECT'cTranNo'AS ColName, TranNo, RowNum FROM tbl
    UNIONSELECT'nTotalSales'AS ColName, nTotalSales, RowNum FROM tbl
    UNIONSELECT'nBalance'AS ColName, nBalance, RowNum FROM tbl
    ORDERBY RowNum, ColName