如何使您的数据水平 [英] how to make your data horizontal
本文介绍了如何使您的数据水平的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在 2 行中有 2 个相同的数据,我打算将这些数据变成 1 行.例如我有这个数据样本
i have 2 identical data in 2 row and i intend to make this data become 1 row. for example i have this data sample
Name Status Bank
Thung Active ABC Bank
Thung Hold ABC Bank
我可以做这样的事情吗
Name Status 1 Bank 1 Status 2 Bank 2
Thung Active ABC Bank Hold ABC Bank
对不起,我无法正确解释
sorry i cant explain it properly
推荐答案
MS SQL Server 2017 架构设置:
create table MyTable(Name varchar(max),BStatus varchar(max),Bank varchar(max))
insert into MyTable (Name,BStatus,Bank)values('Thung','Active', 'ABC Bank')
insert into MyTable (Name,BStatus,Bank)values('Thung','Hold', 'ABC Bank')
查询 1:
with CTE AS (select *,
(CASE WHEN BStatus='Active' THEN BStatus END) AS Status1,
(CASE WHEN BStatus = 'Hold' THEN BStatus END) AS Status2,
(CASE WHEN Bank='ABC Bank' THEN Bank END) AS Bank1,
(CASE WHEN Bank='ABC Bank' THEN Bank END) AS Bank2,
ROW_NUMBER() OVER (PARTITION BY BStatus,Bank Order By Name) as rn
from MyTable
group by Name,BStatus,Bank )
select c.Name
,max(c.Status1) AS Status1
,max(c.Status2) AS Status2
,max(c.Bank1) AS Bank1
,max(c.Bank2) AS Bank2
from cte c
where rn=1
group by c.Name,c.Bank
结果:
| Name | Status1 | Status2 | Bank1 | Bank2 |
|-------|---------|---------|----------|----------|
| Thung | Active | Hold | ABC Bank | ABC Bank |
这篇关于如何使您的数据水平的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文