Swap Columns And Rows

Question

I Have a table like this:

a  |  b  |  c
-------------
1  |  2  |  3

where the first row are column names. I want to swap entire table and converto to this:

col  |  val
-----------
a    |  1
b    |  2
c    |  3

How can I do this by Unpivot in T-SQL?

http://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql — Hockenberry, Mar 08 '15 at 14:06

Stephan · Answer 1 · 2015-03-09T15:04:49.443

1

Try this:

--Create YourTable
SELECT  1 AS a,
        2 AS b,
        3 AS c INTO YourTable

--Unpivot YourTable
SELECT  unpvt.Col,
        unpvt.Val
FROM YourTable
UNPIVOT 
    (Val for Col in (a,b,c)) unpvt

edited Mar 09 '15 at 15:04

answered Mar 09 '15 at 03:49

Stephan

5,891
1
16
24

thanks a lot! what if my val column's type be various? – Hamed Zakery Miab Mar 09 '15 at 05:56

score 1 · Accepted Answer · answered Mar 09 '15 at 15:15

If you have multiple data types, then you can cast the columns as NVARCHAR since most data types can be casted as NVARCHAR like so:

   --Create YourTable
SELECT  N'unicode text' AS a,
        CAST(23123123 AS BIGINT)AS b,
        CAST('20120101' AS DATE) AS c INTO YourTable

--Unpivot YourTable
SELECT  unpvt.Col,
        unpvt.Val
FROM 
--Cast each column as NVARCHAR
(
    SELECT  CAST(a AS NVARCHAR(MAX)) a,
            CAST(b AS NVARCHAR(MAX)) b,
            CAST(c AS nvarchar(MAX)) c
    FROM YourTable
) A
UNPIVOT 
    (Val for Col in (a,b,c)) unpvt

DROP TABLE YourTable

Swap Columns And Rows

2 Answers2