27,579
社区成员
发帖
与我相关
我的任务
分享
-- 应该是去掉空行
-- 小凑个人数,建表语句和 insert 拷自 5# ,我们懒人都这样。。。。。。
create table mytest
(
GA varchar(20)
, FIN varchar(20)
, HR varchar(20)
, VM varchar(20)
)
insert into mytest (GA, FIN, HR, VM)
(
select null, null, '胡XX', null union all
select null, null, null, '赵XX' union all
select null, null, null, null union all
select null, null, null, null union all
select '田XX', null, null, null union all
select null, null, null, '严XX' union all
select null, null, null, null union all
select null, null, null, null union all
select null, null, null, '郭XX' union all
select null, null, null, null union all
select null, null, null, null union all
select null, null, '陈XX', null
)
go
with
a as (select ROW_NUMBER() over(order by ga) as rn , GA from mytest where GA is not null) ,
b as (select ROW_NUMBER() over(order by HR) as rn , HR from mytest where HR is not null) ,
c as (select ROW_NUMBER() over(order by VM) as rn , VM from mytest where VM is not null)
select a.GA , b.HR , c.VM
from a
full join b on a.rn = b.rn
full join c on isnull(a.rn , b.rn) = c.rn
if OBJECT_ID('tempdb..#t') > 0 drop table #t
create table #t
(
GA NVARCHAR(20),
FIN NVARCHAR(20),
HR NVARCHAR(20),
VM NVARCHAR(20)
);
INSERT INTO #t VALUES
(null,null,'A',NULL),
(null,null,NULL,'B'),
(null,null,NULL,NULL),
(null,null,NULL,NULL),
('C',null,NULL,NULL),
(null,null,NULL,'D'),
(null,null,NULL,NULL),
(null,null,NULL,NULL),
(null,null,NULL,'E'),
(null,null,NULL,NULL),
(null,null,NULL,NULL),
(null,null,'F',NULL);
WITH CTE AS
(
select ROW_NUMBER()over(PARTITIOn by bu order by val) id,* from #t
unpivot
(val for bu in(ga,fin,hr,vm))b
)
select * from CTE
pivot
(min(val) for bu in(ga,hr,vm))b
-- 测试数据
declare @TestData table
(
GA varchar(20)
, FIN varchar(20)
, HR varchar(20)
, VM varchar(20)
)
insert into @TestData (GA, FIN, HR, VM)
(
select null, null, '户', null union all
select null, null, null, '赵' union all
select null, null, null, null union all
select null, null, null, null union all
select '天', null, null, null union all
select null, null, null, '眼' union all
select null, null, null, null union all
select null, null, null, null union all
select null, null, null, '过' union all
select null, null, null, null union all
select null, null, null, null union all
select null, null, '陈', null
);
-- 列转行
with T1 as
(
select *
from
(
select *
from @TestData
) x
unpivot
(
val for ky in (GA, FIN, HR, VM)
) y
)
-- 分组
, T2 as
(
select *
, rn = row_number() over(partition by ky order by val)
from T1
)
-- 行转列
select *
from
(
select *
from T2
) x
pivot
(
max(val) for ky in (GA, FIN, HR, VM)
) y
-- 测试数据
declare @TestData table
(
GA varchar(20)
, FIN varchar(20)
, HR varchar(20)
, VM varchar(20)
)
insert into @TestData (GA, FIN, HR, VM)
(
select null, null, '户', null union all
select null, null, null, '赵' union all
select null, null, null, null union all
select null, null, null, null union all
select '天', null, null, null union all
select null, null, null, '眼' union all
select null, null, null, null union all
select null, null, null, null union all
select null, null, null, '过' union all
select null, null, null, null union all
select null, null, null, null union all
select null, null, '陈', null
);
-- 列转行
with T1 as
(
select *
from
(
select GA = convert(varchar(max),GA)
, FIN = convert(varchar(max),FIN)
, HR = convert(varchar(max),HR)
, VM = convert(varchar(max),VM)
from @TestData
) x
unpivot
(
val for ky in (GA, FIN, HR, VM)
) y
)
-- 分组
, T2 as
(
select *
, rn = row_number() over(partition by ky order by val)
from T1
)
-- 行转列
select *
from
(
select *
from T2
) x
pivot
(
max(val) for ky in (GA, FIN, HR, VM)
) y