Get the rows of my output sorted in ascending order from this table?

I am using SQL Server 2014 and I have the following table (t1) which contains around 5,000 rows. Here is an extract of t1:

 n1   n2   n3   n4   n5   n6  Type
 6    15   10   11   22   25   C1
 2     5    1   20   21   30   C1
15    25   18    4   12   38   C2
11     1    4    9   24   31   C2
...

I need select all the rows from the table but I want the output to be sorted in ascending for each row.

Here is the expected output:

 Nr1   Nr2   Nr3  Nr4   Nr5  Nr6   Type
  6    10    11   15    22   25     C1
  1     2     5   20    21   30     C1
  4    12    15   18    25   38     C2
  1     4     9   11    24   31     C2
  ...

I know how to ORDER BY columns but I am completely stuck as to how to approach this problem. Do I need to pivot the data, sort it and then unpivot it to achieve this?

Note: I have added an Id column to my column. Each row now has a unique Id (1,2,3,…)

My attempt as per Menno’s Solution:

;with cte1 as (

SELECT [id], [type], Col, Val
FROM (  
  SELECT [id], [n1], [n2], [n3], [n4], [n5], [n6], [type]
  FROM [t1] ) t
UNPIVOT
   (Col FOR Val IN ([n1], [n2], [n3], [n4], [n5], [n6]))
AS tblUnPivot

),

cte2 as (

Select

ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Val) OrderedVals, * 

from [cte1]

),

cte3 as (

SELECT [id], [type], Col, Val
FROM (  
  SELECT [OrderedVals], [id], [type], [Col]
  FROM [cte2] ) s
PIVOT
   (??? FOR OrderedVals IN ([1],[2],[3],[4],[5],[6]))

AS tblPivot

)

select * from [cte3]

Answer

Assuming that there is some kind of ID column, then you can unpivot the values, and the Pivot them back using VALUES and a Cross Tab:

CREATE TABLE dbo.YourTable (ID int IDENTITY, --Required to work
                            n1 tinyint,
                            n2 tinyint,
                            n3 tinyint,
                            n4 tinyint,
                            n5 tinyint,
                            n6 tinyint,
                            [Type] char(2));
GO

INSERT INTO dbo.YourTable (n1,n2, n3, n4, n5, n6, [Type])
VALUES( 6,15,10,11,22,25,'C1'),
      ( 2, 5, 1,20,21,30,'C1'),
      (15,25,18, 4,12,38,'C2'),
      (11, 1, 4, 9,24,31,'C2');

GO

SELECT *
FROM dbo.YourTable;

WITH unPvt AS (
    SELECT YT.ID,
           YT.[Type],
           V.Val,
           ROW_NUMBER() OVER (PARTITION BY YT.ID ORDER BY V.Val) AS NewPos
    FROM dbo.YourTable YT
         CROSS APPLY (VALUES(1,YT.n1),
                            (2,YT.n2),
                            (3,YT.n3),
                            (4,YT.n4),
                            (5,YT.n5),
                            (6,YT.n6))V(Pos,Val))
SELECT MAX(CASE NewPos WHEN 1 THEN Val END) AS n1,
       MAX(CASE NewPos WHEN 2 THEN Val END) AS n2,
       MAX(CASE NewPos WHEN 3 THEN Val END) AS n3,
       MAX(CASE NewPos WHEN 4 THEN Val END) AS n4,
       MAX(CASE NewPos WHEN 5 THEN Val END) AS n5,
       MAX(CASE NewPos WHEN 6 THEN Val END) AS n6,
       [Type]
FROM unPvt
GROUP BY ID,[Type]
ORDER BY [Type];


GO
DROP TABLE dbo.YourTable;