sql - 如何根据相关列的值将值从行旋转到列
问题描述
我正在尝试基于 3 个值来旋转一组数据,以便每一行代表与每个 ID 相关的所有配置文件 URL,每列与配置文件代码相关
这个问题与我想要实现的非常相似,但是它没有对基于另一个值进行透视的列进行分组:Pivot values on column based on grouped columns in SQL
因此,给出以下示例表:
Id ProfileCode ProfileURL
-------------------------------------------------------
7ADC7368 IA http://www.domainIA.com/profile1
5C627D6F IA http://www.domainIA.com/profile2
5C627D6F AG http://www.domainAG.com/profile1
5C627D6F AF http://www.domainAF.com/profile1
664B4AE9 IA http://www.domainIA.com/profile3
664B4AE9 AF http://www.domainAF.com/profile2
我希望将其转换为下表:
Id IA AG AF
-------------------------------------------------------------------------------------------------------------
7ADC7368 http://www.domainIA.com/profile1 null null
5C627D6F http://www.domainIA.com/profile2 http://www.domainAG.com/profile1 http://www.domainAF.com/profile1
664B4AE9 http://www.domainIA.com/profile3 null http://www.domainAF.com/profile2
这是我一直在尝试使用的代码,但我找不到将枢轴与配置文件 URL 与其关联的配置文件代码之间的关联相关联的方法。
declare @tmp TABLE (Id NVARCHAR(15), ProfileCode NVARCHAR(2), ProfileURL NVARCHAR(50))
insert into @tmp (Id, ProfileCode, ProfileURL)
values ('7ADC7368', 'IA', 'http://www.domainIA.com/profile1'),
('5C627D6F', 'IA', 'http://www.domainIA.com/profile2'),
('5C627D6F', 'AG', 'http://www.domainAG.com/profile1'),
('5C627D6F', 'AF', 'http://www.domainAF.com/profile1'),
('664B4AE9', 'IA', 'http://www.domainIA.com/profile3'),
('664B4AE9', 'AF', 'http://www.domainAF.com/profile2')
select
pvt.id,
CASE
WHEN ProfileCode = 'IA' THEN ProfileURL
END AS 'IA',
CASE
WHEN ProfileCode = 'AF' THEN ProfileURL
END AS 'AF',
CASE
WHEN ProfileCode = 'AG' THEN ProfileURL
END AS 'AG'
from (
select
Id, ProfileCode, ProfileURL
,ROW_NUMBER() over(partition by ProfileCode order by ProfileURL) as RowNum
from
@tmp
) a
pivot (MAX(ProfileCode) for RowNum in ('IA', 'AF', 'AG') as pvt
对于我正在努力实现的目标,我将不胜感激。
解决方案
只需使用条件聚合:
SELECT id,
MAX(CASE WHEN ProfileCode = 'IA' THEN ProfileURL END) AS IA,
MAX(CASE WHEN ProfileCode = 'AF' THEN ProfileURL END) AS AF,
MAX(CASE WHEN ProfileCode = 'AG' THEN ProfileURL END) AS AG
FROM @tmp t
GROUP BY id;
仅ROW_NUMBER()
当给定 id 有多个相同的代码并且您希望结果位于不同的行时,您才需要。您的示例数据和当前逻辑表明情况并非如此。
推荐阅读
- mysql - 在带值 X 和效率的行之后选择带值 Y 的行
- c# - 使用官方 .NET SDK 在 linux 上通过 cli 编译 c# 代码
- time-series - 多年重复一年时间序列
- reactjs - Why there is no icons of fullscreen on my map?
- javascript - NaN 有问题。试图修复它,添加了一个 gazillion parseInts 和 parseFloats 来尝试修复它,没有工作
- typescript - How can I exported a type from a Vue 3 single file Typescript component?
- git - 仓库中的 git repo
- node.js - Apollo Federated Gateway returns an error when trying to use a .NET Core GraphQL federated subgraph
- java - How to only add a number to a vector if it doesn't already exist in it?
- c# - C# accessing anArraylist of floats