How to order UNPIVOT

Question

I have the following UNPIVOT code and I would like to order it by the FactSheetSummary columns so that when it is converted to rows it is order 1 - 12:

INSERT INTO #Results
SELECT DISTINCT ReportingDate, PortfolioID,ISIN, PortfolioNme, Section,REPLACE(REPLACE(Risks,'‘',''''),'’','''')
FROM  
(SELECT  DISTINCT
            ReportingDate
        ,   PortfolioID
        ,   ISIN
        ,   PortfolioNme
        ,   Section
        ,   FactSheetSummary_1, FactSheetSummary_2, FactSheetSummary_3
        ,   FactSheetSummary_4, FactSheetSummary_5, FactSheetSummary_6  
        ,   FactSheetSummary_7, FactSheetSummary_8, FactSheetSummary_9  
        ,   FactSheetSummary_10, FactSheetSummary_11, FactSheetSummary_12   
  FROM #WorkingTableFactsheet) p
UNPIVOT
(Risks FOR FactsheetSummary IN 
    (   FactSheetSummary_1, FactSheetSummary_2, FactSheetSummary_3
        ,   FactSheetSummary_4, FactSheetSummary_5, FactSheetSummary_6  
        ,   FactSheetSummary_7, FactSheetSummary_8, FactSheetSummary_9  
        ,   FactSheetSummary_10, FactSheetSummary_11, FactSheetSummary_12)
 )AS unpvt;

--DELETE records where there are no Risk Narratives
DELETE FROM #Results
WHERE Risks = ''

SELECT 
ReportingDate
,   PortfolioID
,   ISIN
,   PortfolioNme
,   Section
,   Risks   
,   ROW_NUMBER() OVER(PARTITION BY ISIN,Section ORDER BY ISIN,Section,Risks) as SortOrder
FROM #Results
order by ISIN,  Risks

Is it possible to do this? I thought the IN of the UNPIVOT would dictate the order? Do I need to add a column to dictate which I would like to be 1 through to 12?

Answer 1

Just use case expression to order:

order by case FactsheetSummary  
          when 'FactSheetSummary_1' then 1
          when 'FactSheetSummary_2' then 2
          when 'FactSheetSummary_12' then 12 end