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.

Try changing your `SELECT *` in the top-most query to your sort order. There aren't that many columns to type out. — Kramb, Jun 30 '16 at 13:02

score 1 · Accepted Answer · answered Jun 30 '16 at 13:03

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

@KhanImranAli huh? I might need more caffeine but I can't see the spelling mistake there — Arthur D, Jun 30 '16 at 13:30
Thanks "Arthur D" works like a smooth criminal. @KhanImranAli Dude you need some rest. — Tony smith, Jul 01 '16 at 06:39

score 0 · Answer 2 · edited May 16 '18 at 17:54

    create table #objective
              (obj varchar(100),ord int)

    insert into #objective (obj,ord) values('TrainingDoneThisMonth',1)
    insert into #objective (obj,ord) values('FoodQualityStatus',2)

    insert into #objective (obj,ord) values('NoOfAllDrugTests',3)
`  `insert into #objective (obj,ord) values('NoOfAllDrugTests',4)

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 * into #ajay
    --, 
    --      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 

select #ajay.*,ord from #ajay
join objective on objective.obj=#ajay.[Objective]
order by ord
DROP TABLE #myTable
DROP TABLE #ajay

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

2 Answers2