sql - 在 SQL 中将多行转换为多列
问题描述
我有这样的数据:
Team Month Count_of_Stores
a 4 10
b 4 4
c 4 6
a 5 8
b 5 14
e 5 9
a 6 7
b 6 3
f 6 1
我正在努力获得类似于将行转换为列的输出:
团队月 Count_of_Stores 团队月 Count_of_Stores 团队月 Count_of_Stores a 4 10 a 5 8 a 6 7 b 4 4 b 5 14 b 6 3 c 4 6 e 5 9 f 6 1
我确信枢轴在这里应该有很大的帮助,但在这里的适当用法却很困惑。任何帮助深表感谢。
解决方案
我正在根据提供的数据推断团队关联。像下面这样的东西可以为你工作(演示:http ://rextester.com/GQHPV34978 )
CREATE TABLE temp
(
[teamID] INT,
[month] INT,
[count_of_stores] INT
)
INSERT INTO temp
VALUES
(1,4,10),
(2,4,4),
(3,4,6),
(1,5,8),
(2,5,14),
(3,5,9),
(1,6,7),
(2,6,3),
(3,6,1)
SELECT [teamID],
MAX(CASE WHEN MONTH = 4 THEN MONTH END )AS Month,
MAX(CASE WHEN MONTH = 4 THEN count_of_stores END ) AS Count_of_Stores,
MAX(CASE WHEN MONTH = 5 THEN MONTH END )AS Month,
MAX(CASE WHEN MONTH = 5 THEN count_of_stores END ) AS Count_of_Stores ,
MAX(CASE WHEN MONTH = 6 THEN MONTH END )AS Month,
MAX(CASE WHEN MONTH = 6 THEN count_of_stores END ) AS Count_of_Stores
FROM temp
GROUP BY teamID
根据新信息更新以下内容(演示:http ://rextester.com/JIZQX61960 )
create TABLE #temp
(
[teamID] varchar,
[month] INT,
[count_of_stores] INT
)
INSERT INTO #temp
VALUES
('a',4,10),
('b',4,4),
('c',4,6),
('a',5,8),
('b',5,14),
('e',5,9),
('a',6,7),
('b',6,3),
('f',6,1);
WITH monthGrouping AS
(
SELECT row_number() over (partition by month order by month) as rn, [teamID], [month],[count_of_stores] FROM #temp
)
SELECT
MAX(CASE WHEN MONTH = 4 THEN [teamID] END )AS [teamID],
MAX(CASE WHEN MONTH = 4 THEN MONTH END )AS Month,
MAX(CASE WHEN MONTH = 4 THEN count_of_stores END ) AS Count_of_Stores,
MAX(CASE WHEN MONTH = 5 THEN [teamID] END )AS [teamID],
MAX(CASE WHEN MONTH = 5 THEN MONTH END )AS Month,
MAX(CASE WHEN MONTH = 5 THEN count_of_stores END ) AS Count_of_Stores ,
MAX(CASE WHEN MONTH = 6 THEN [teamID] END )AS [teamID],
MAX(CASE WHEN MONTH = 6 THEN MONTH END )AS Month,
MAX(CASE WHEN MONTH = 6 THEN count_of_stores END ) AS Count_of_Stores
FROM monthGrouping
GROUP BY rn
推荐阅读
- python - Beautifulsoup 如何从特定类中提取文本
- corda - 如何为 FungibleToken 创建新命令
- node.js - 为什么端口会在谷歌云上自动关闭?
- swift - 需要初始化程序设置以始终设置属性
- reactjs - Napster API 身份验证 - 错误请求/未经授权
- php - Laravel Eloquent 查询条件并使用 sum 和 groupBy
- database - 如何将我的 Oracle 数据库导出为文件?
- node.js - Mongoose 文档的递归更新
- c# - 如何从 identityserver4 自定义表名?
- flutter - Flutter 可以替代 PRN 堆栈中的 React 吗?