sql 表旋转

最新推荐文章于 2024-04-18 21:03:55 发布

Fergus awsl

最新推荐文章于 2024-04-18 21:03:55 发布

阅读量251

点赞数

分类专栏： Exercise

本文链接：https://blog.csdn.net/weixin_43650411/article/details/114702425

版权

Exercise 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

在这里插入图片描述

/* MSSQL */
SELECT id,
		[Jan] AS [Jan_Revenue],
		[Feb] AS [Feb_Revenue],
		[Mar] AS [Mar_Revenue],
		[Apr] AS [Apr_Revenue],
		...
	FROM Department
	PIVOT
	(
		sum(revenue)
		FOR month in ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])
	)AS pivot_month

# Write your MySQL query statement below

SELECT id,
        sum(CASE month WHEN 'Jan' THEN revenue END) AS Jan_Revenue,
        sum(CASE month WHEN 'Feb' THEN revenue END) AS Feb_Revenue,
        sum(CASE month WHEN 'Mar' THEN revenue END) AS Mar_Revenue,
        sum(CASE month WHEN 'Apr' THEN revenue END) AS Apr_Revenue,
        sum(CASE month WHEN 'May' THEN revenue END) AS May_Revenue,
        sum(CASE month WHEN 'Jun' THEN revenue END) AS Jun_Revenue,
        sum(CASE month WHEN 'Jul' THEN revenue END) AS Jul_Revenue,
        sum(CASE month WHEN 'Aug' THEN revenue END) AS Aug_Revenue,
        sum(CASE month WHEN 'Sep' THEN revenue END) AS Sep_Revenue,
        sum(CASE month WHEN 'Oct' THEN revenue END) AS Oct_Revenue,
        sum(CASE month WHEN 'Nov' THEN revenue END) AS Nov_Revenue,
        sum(CASE month WHEN 'Dec' THEN revenue END) AS Dec_Revenue
    FROM Department
    GROUP BY id