将行更改为列

Question

我需要从下面的tABLE按模型翻转行和列

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

我想要一个sql查询来翻转表(列为行)

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

Answer 1

你可以试试这个，效果很好。

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 

Answer 2

有很多关于它的参考资料：

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/