sql - 如何将6列按数字顺序排序

Question

我正在6列中获取数据。如何将这些列排序为它们的值并将它们存储到新列或同一列中。

Input
ID  N1 N2 N3 N4 N5 N6
1   6  4  5  3  1  2
2   42 43 46 45 48 47

Desired Output
ID  N1 N2 N3 N4 N5 N6  S1 S2 S3 S4 S5 S6
1   6  4  5  3  1  2   1  2  3  4  5  6
2   42 43 46 45 48 47  42 43 45 46 47 48 

Answer 1

select T.ID, T.N1, T.N2, T.N3, T.N4, T.N5, T.N6, 
       N.X.value('N[1]', 'int') as S1,
       N.X.value('N[2]', 'int') as S2,
       N.X.value('N[3]', 'int') as S3,
       N.X.value('N[4]', 'int') as S4,
       N.X.value('N[5]', 'int') as S5,
       N.X.value('N[6]', 'int') as S6
from YourTable as T
cross apply
  (
    select N
    from (values (N1),(N2),(N3),(N4),(N5),(N6)) as N(N)
    order by N.N
    for xml path(''), type
  ) as N(X)

SE-Data

一样，只是使用 pivot而不是XML。

select P.ID, P.N1, P.N2, P.N3, P.N4, P.N5, P.N6,
       P.[1] as S1,
       P.[2] as S2,
       P.[3] as S3,
       P.[4] as S4,
       P.[5] as S5,
       P.[6] as S6
from
  (
    select T.ID, T.N1, T.N2, T.N3, T.N4, T.N5, T.N6, N.N, N.rn
    from YourTable as T
    cross apply
      (
        select N,
               row_number() over(order by N.N) as rn
        from (values (N1),(N2),(N3),(N4),(N5),(N6)) as N(N)
      ) N
  ) as T
pivot (min(T.N) for T.rn in ([1],[2],[3],[4],[5],[6])) as P

SE-Data