changing rows to columns

Question

I need to flip the rows and columns by MODEL from the below tABLE

------------------------
MODEL   YEAR    PRICE
------------------------
AUDI    2012    45 LAKHS
BENZ    2013    40 LAKHS
BMW     2011    38 LAKHS

I want a sql query to flip the table (rows as coloumns)

---------------------------------------
Model      AUDI      BENZ     BMW 
----------------------------------
Year       2012      2013     2011
Price      45Lakhs   40Lakhs  38Lakhs

http://stackoverflow.com/questions/13031846/manipulate-results-to-display-rows-as-columns — JsonStatham, Jul 08 '13 at 11:18
http://stackoverflow.com/questions/10699997/sql-server-transpose-rows-into-columns — Md. Parvez Alam, Jul 08 '13 at 12:04

score 1 · Accepted Answer · answered Jul 08 '13 at 14:24

You can Try this it perfectly works

CREATE TABLE SOMEDATA(
                        MODEL varchar(10), 
                        YEAR  int, 
                        PRICE int
                        )

INSERT INTO SOMEDATA VALUES('AUDI'  ,   2012  ,  45)
INSERT INTO SOMEDATA VALUES('BENZ'  ,   2013  ,  40)
INSERT INTO SOMEDATA VALUES('BMW'  ,   2011  ,  38)


select * from SOMEDATA 

SELECT 'Year' as Model, AUDI, BENZ,BMW
FROM (
SELECT MODEL, YEAR
FROM SOMEDATA) up
PIVOT (avg(YEAR) FOR MODEL IN (AUDI, BENZ,BMW )) AS pvt

UNION  ALL 

SELECT 'Price' as Model, AUDI, BENZ,BMW
FROM (
SELECT MODEL,Price
FROM SOMEDATA) up
PIVOT (avg(Price) FOR MODEL IN (AUDI, BENZ,BMW )) AS pvt

drop table SOMEDATA

score 0 · Answer 2 · edited May 23 '17 at 12:29

0

There are tons of references about it:

how to select columns as rows?

Rows in columns

https://www.simple-talk.com/sql/t-sql-programming/creating-cross-tab-queries-and-pivot-tables-in-sql/

edited May 23 '17 at 12:29

Community

1
1

answered Jul 08 '13 at 10:56

asafrob

1,838
13
16

changing rows to columns

2 Answers2