原数据每行的数字大小顺序混乱，通过行专列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

想要的结果

这题我一看就感觉不对劲,你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

下面是实测截图

---

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

实测截图

思路都是列转行，然后用排序后的序号作为列名，再转回来，两种做法供参考：

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'

--方法1
SELECT  *
FROM    (   SELECT  t.ID, t2.*, 'N' + CAST(ROW_NUMBER() OVER (PARTITION BY t.ID ORDER BY t2.x) AS VARCHAR(2)) seq
            FROM    #test t
                    CROSS APPLY (VALUES (t.n1), (t.n2), (t.n3), (t.n4), (t.n5), (t.n6), (t.n7), (t.n8), (t.n9), (t.n10), (t.n11), (t.n12), (t.n13), (t.n14), (t.n15), (t.n16), (t.n17), (t.n18), (t.n19), (t.n20)) t2 (x) ) tt
PIVOT (   MAX(tt.x)
          FOR seq IN (N1, N2, N3, N4, N5, N6, N7, N8, N9, N10, N11, N12, N13, N14, N15, N16, N17, N18, N19, N20)) p;
--方法2
;WITH t1 AS (SELECT m.ID, m.val, 'N' + CAST(ROW_NUMBER() OVER (PARTITION BY ID ORDER BY val) AS VARCHAR(2)) 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    t1 m
PIVOT (   MAX(val)
          FOR RID IN (n1, n2, n3, n4, n5, n6, n7, n8, n9, n10, n11, n12, n13, n14, n15, n16, n17, n18, n19, n20)) p;