sql-serverpivotunpivot

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


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.


Solution

  • 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