RICHEER COCA 2021-12-31 20:24 采纳率: 93.9%
浏览 31
已结题

原数据每行的数字大小顺序混乱,通过行专列VOL升序,转回行 数据出错,请专家看看


 
DROP TABLE IF EXISTS #test
 
create table #test([ID] [int] IDENTITY(1,1) NOT NULL primary key,n1 INT,n2 INT,n3 INT,n4 INT,n5 INT,n6 INT,n7 INT,n8 INT,n9 INT,n10 INT,n11 INT,n12 INT,n13 INT,n14 INT,n15 INT,n16 INT,n17 INT,n18 INT,n19 INT,n20 INT)  
insert into #test 
SELECT '2','9','13','16','34','43','50','54','77','3','6','12','35','44','18','22','24','41','76','80'
UNION ALL SELECT '2','9','13','16','34','43','50','54','77','3','12','35','36','44','18','22','24','41','76','80'
UNION ALL SELECT '2','9','13','16','34','43','50','54','77','3','12','35','44','56','18','22','24','41','76','80'
UNION ALL SELECT '2','9','13','16','34','43','50','54','77','3','12','35','44','60','18','22','24','41','76','80'
UNION ALL SELECT '2','9','13','16','34','43','50','54','77','3','12','35','44','61','18','22','24','41','76','80'


  --  select * from #test 
;with t1 as (  
select *,ROW_NUMBER() over(PARTITION by ID order by val ) as RID 
from #test 
unpivot(val for col in (n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,n13,n14,n15,n16,n17,n18,n19,n20)) m
)
select * from(
select t.*
from t1 t)m
pivot(max(ID) for col in (n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,n13,n14,n15,n16,n17,n18,n19,n20)) p

想要的结果

img

  • 写回答

2条回答 默认 最新

  • DarkAthena ORACLE应用及数据库设计方案咨询师 2021-12-31 21:20
    关注

    这题我一看就感觉不对劲,你unpivot转一次又pivot转回去,真能得到这个样子的数据?
    这sql要不报错,可参考官方文档


    对于sqlserver来说,pivot里in的值是要打中括号的

    
    with t1 as (  
    select *,ROW_NUMBER() over(PARTITION by ID order by val ) as RID 
    from test 
    unpivot(val for col in (n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,n13,n14,n15,n16,n17,n18,n19,n20)) m
    )
    select * from(
    select t.*
    from t1 t)m
    pivot(max(ID) for col in ([n1] ,[n2],[n3],[n4],[n5],[n6],[n7],[n8],[n9],[n10],[n11],
                              [n12],[n13],[n14],[n15],[n16],[n17],[n18],[n19],[n20])) p
    

    下面是实测截图

    img


    你用原列,怎么转都会转回去,我把你的sql 的for 改了,重新定义列

    
    with t1 as (  
    select *,ROW_NUMBER() over(PARTITION by ID order by val ) as RID 
    from test 
    unpivot(val for col in (n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,n13,n14,n15,n16,n17,n18,n19,n20)) m
    ), t2 as (
    select * from(
    select t.*
    from t1 t)m
    pivot(max(val) for rid in ([1] ,[2] ,[3],[4],[5],[6],[7],[8],[9],[10],[11],
                              [12],[13],[14],[15],[16],[17],[18],[19],[20])) p)
    select id, 
    max([1]) as n1,max([2]) as n2,max([3]) as n3,max([4]) as n4,max([5]) as n5,
    max([6]) as n6,max([7]) as n7,max([8]) as n8,max([9]) as n9,max([10]) as n10,max([11]) as n11,
    max([12]) as n12,max([13]) as n13,max([14]) as n14,max([15]) as n15,max([16]) as n16,
    max([17]) as n17,max([18]) as n18,max([19]) as n19,max([20])  as n20 from t2                         
    group by id
    

    实测截图

    img

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论 编辑记录
查看更多回答(1条)

报告相同问题?

问题事件

  • 系统已结题 1月11日
  • 已采纳回答 1月3日
  • 创建了问题 12月31日

悬赏问题

  • ¥15 MATLAB yalmip gurobi求解器
  • ¥15 关于#stm32#的问题:第一张图是发送数据代码,第二第三张图是接收数据并进行DAC转换的函数,若用串口一接收数据并进行DAC转换,主函数代码该怎么写
  • ¥20 hc130怎么读写内部flash存储信息
  • ¥15 Axure rp9注册与登录交互
  • ¥15 我下载图形界面重启完就变成这样了,打字也打不了,动也动不了,该怎么解决(操作系统-centos)
  • ¥15 VBA中在窗体中遍历所有checkbox控件,提取出被选中的checkbox的caption值
  • ¥15 在Ubuntu上有什么命令,或者是系统文件能告诉我链接nvme ssd的pcie槽位是不是支持热插拔功能?
  • ¥15 ansys license许可证问题
  • ¥20 QQ号和密码都能正常登录微信 QQ号和密码登录微信显示密码错误
  • ¥15 单片机RTOS Kernel与应用分离开发,Kernel如何调起应用?