Transform row to column in sql

Question

I have table like this:

PC(code, model, speed, ram, hd, cd, price)

when i run the query :

select model,speed,ram,hd,cd,price from PC where code=(select max(code) from PC) i get the result as below:

model   speed   ram   hd    cd     price
1233    800     128  20.0   50x  970.0000

But i need the result using unpivot:

chr    value
cd      50x
hd      20
model   1233
price   970.00
ram     128
speed   800

I am using sql server 2014.

what version of sql are you using? mysql, sqlserver, oracle? — Jeremy C., May 21 '15 at 07:35
you already tagged the answer - UNPIVOT. See: http://www.sqlservercurry.com/2011/01/unpivot-example-in-sql-server.html — CeOnSql, May 21 '15 at 07:45

score 1 · Accepted Answer · edited May 21 '15 at 08:21

If you consider this table and these test values:

DECLARE @tbl TABLE(model INT,speed INT,ram INT,hd FLOAT,cd VARCHAR(10),price FLOAT)
INSERT INTO @tbl
VALUES
(1233,800,128,20.0,'50x',970.0000)

You could do something like this:

SELECT
    unpvt.chr,
    unpvt.value
FROM
(
    SELECT 
        CAST(model AS VARCHAR(10)) AS model,
        CAST(speed AS VARCHAR(10)) AS speed,
        CAST(ram AS VARCHAR(10)) AS ram,
        CAST(hd AS VARCHAR(10)) AS hd,
        cd,
        CAST(price AS VARCHAR(10)) AS price
    FROM 
        @tbl AS t
) AS sourceTable
UNPIVOT
(
    value 
    FOR chr IN(model,speed,ram,hd,cd,price)
)
AS unpvt
ORDER BY unpvt.chr

This will result in this output:

chr     value
---------------
cd      50x
hd      20
model   1233
price   970
ram     128
speed   800

You can see it in this SQLFIDDLE.

Transform row to column in sql

1 Answers1