How to manually set sort order of Unpivot and pivot in sql server

Question

Hi I have applied Unpivot and pivot to my data. All is going well excepts I want to arrange my output in the same order as specified in "IN" clause of unpivot. Please help. Here is what I have done so far:

    CREATE TABLE #myTable
    (
    [ForYear] [smallint] NOT NULL,
    [ForMonth] [tinyint] NOT NULL,
    [TrainingDoneThisMonth] [bit] NULL,
    [FoodQualityStatus] [bit] NULL,
    [NoOfAllDrugTests] [int] NULL,
    [NoOfAllAlcoholTests] [int] NULL
    )


    INSERT INTO #myTable 
    values
    (2016,1,1,0,5,10),
    (2016,2,0,1,15,5),
    (2016,3,1,0,20,15),
    (2016,4,0,1,5,25),
    (2016,5,1,0,10,30),
    (2015,1,1,0,5,10),
    (2015,2,0,1,15,5),
    (2015,3,1,0,20,15),
    (2015,4,0,1,5,25),
    (2015,5,1,0,10,30)
select * from(SELECT *
    FROM (
        SELECT  DATENAME(month,DATEADD(month,[ForMonth]-1,'1970-01-01')) as d,
                CAST([TrainingDoneThisMonth] as int) as [TrainingDoneThisMonth],
                CAST([FoodQualityStatus] as int) as [FoodQualityStatus],
                [NoOfAllDrugTests],
                [NoOfAllAlcoholTests]

        FROM #myTable
        WHERE foryear=2016
        ) d
    UNPIVOT (
      [VALUES] FOR [Objective] in ([TrainingDoneThisMonth],[FoodQualityStatus],[NoOfAllDrugTests],[NoOfAllAlcoholTests])
    ) unpvt
) as p
PIVOT (
    SUM([VALUES]) FOR d IN ([January],[February],[March],[April],[May])
) as pvt

I need result in this order: [TrainingDoneThisMonth],[FoodQualityStatus],[NoOfAllDrugTests],[NoOfAllAlcoholTests]

I have tried : SQL Server , restrict UNPIVOT to order columns automatically but unable to make it working.

Answer 1

Try this:

      CREATE TABLE #myTable
        (
        [ForYear] [smallint] NOT NULL,
        [ForMonth] [tinyint] NOT NULL,
        [TrainingDoneThisMonth] [bit] NULL,
        [FoodQualityStatus] [bit] NULL,
        [NoOfAllDrugTests] [int] NULL,
        [NoOfAllAlcoholTests] [int] NULL
        )


        INSERT INTO #myTable 
        values
        (2016,1,1,0,5,10),
        (2016,2,0,1,15,5),
        (2016,3,1,0,20,15),
        (2016,4,0,1,5,25),
        (2016,5,1,0,10,30),
        (2015,1,1,0,5,10),
        (2015,2,0,1,15,5),
        (2015,3,1,0,20,15),
        (2015,4,0,1,5,25),
        (2015,5,1,0,10,30)
    select *, 
          CASE WHEN objective = 'TrainingDoneThisMonth' THEN 1 
               WHEN objective = 'FoodQualityStatus' THEN 2 
               WHEN objective = 'NoOfAllDrugTests' THEN 3 
               WHEN objective = 'NoOfallAlcoholTests' THEN 4 
           ELSE 5 END AS [ranking]
 from(SELECT *
        FROM (
            SELECT  DATENAME(month,DATEADD(month,[ForMonth]-1,'1970-01-01')) as d,
                    CAST([TrainingDoneThisMonth] as int) as [TrainingDoneThisMonth],
                    CAST([FoodQualityStatus] as int) as [FoodQualityStatus],
                    [NoOfAllDrugTests],
                    [NoOfAllAlcoholTests]

            FROM #myTable
            WHERE foryear=2016
            ) d
        UNPIVOT (
          [VALUES] FOR [Objective] in ([TrainingDoneThisMonth],[FoodQualityStatus],[NoOfAllDrugTests],[NoOfAllAlcoholTests])
    ) unpvt
) as p
PIVOT (
    SUM([VALUES]) FOR d IN ([January],[February],[March],[April],[May])
) as pvt
ORDER BY ranking 
DROP TABLE #myTable