mysql 查询行转列_SQL语句实现行转列查询

表sales

查询结果如下：

1、建表

CREATE TABLE [dbo].[sales](

[id] [int] IDENTITY(1,1) NOT NULL,

[year] [int] NULL,

[jidu] [int] NULL,

[jine] [int] NULL,

PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

2、插入数据

INSERT INTO sales(year, jidu, jine) VALUES(1991,1,11)

INSERT INTO sales(year, jidu, jine) VALUES(1991,2,22)

INSERT INTO sales(year, jidu, jine) VALUES(1991,3,33)

INSERT INTO sales(year, jidu, jine) VALUES(1991,4,44)

INSERT INTO sales(year, jidu, jine) VALUES(1992,1,55)

INSERT INTO sales(year, jidu, jine) VALUES(1992,2,66)

INSERT INTO sales(year, jidu, jine) VALUES(1992,3,77)

INSERT INTO sales(year, jidu, jine) VALUES(1992,4,88)

有两种解决方法：

一、使用case when 实现：

SELECT

year as '年份',

max(CASE jidu WHEN 1 THEN jine END) AS '第一季度',

max(CASE jidu WHEN 2 THEN jine END) AS '第二季度',

max(CASE jidu WHEN 3 THEN jine END) AS '第三季度',

max(CASE jidu WHEN 4 THEN jine END) AS '第四季度'

FROM sales group by year

二、使用pivot函数：

使用pivot函数时须注意对升级到 SQL Server 2005 或更高版本的数据库使用 PIVOT 和 UNPIVOT 时，必须将数据库的兼容级别设置为 90 或更高。

SQL语句如下：

select year as 年份,[1] as 第一季度,[2] as 第二季度,[3] as 第三季度,[4] as 第四季度

from (select year,jidu,jine from sales) as t1 pivot (SUM(jine) FOR jidu IN ([1],[2],[3],[4])) as a --'t1'和'a'这两个别名必须得有要不会报错。

执行结果如下：