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

Question

 
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

想要的结果

Answer 1

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

Using PIVOT and UNPIVOT - SQL Server | Microsoft Docs Transact-SQL reference for PIVOT and UNPIVOT relational operators. Use these operators on SELECT statements to change a table-valued expression into another table. https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15

对于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

下面是实测截图

---

你用原列,怎么转都会转回去,我把你的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

实测截图