Using Pivot function In SQL Server

I have the below table

Code            Data
SL Payroll       1
GV Payroll       3
Global Payroll   1
TimeHCM          1
SL Payroll       0
GV Payroll       0
Global Payroll  0
TimeHCM          0
SL Payroll       0
GV Payroll       0
Global Payroll   0
TimeHCM          0

​x
 
Code            DataSL Payroll       1GV Payroll       3Global Payroll   1TimeHCM          1SL Payroll       0GV Payroll       0Global Payroll  0TimeHCM          0SL Payroll       0GV Payroll       0Global Payroll   0TimeHCM          0​

I am using the pivot function to flatten the data

Select *
  From (
        Select [Code]
              ,[Data]
              ,[Col] = concat('Data',Row_Number() over (Partition By [Code] Order by 1/0))
         From  #BidStatusCalculation
       ) src
 Pivot (max([Data]) for [Col] in ([Data1],[Data2],[Data3],[Data4],[Data5],[Data6],[Data7],[Data8],[Data9],[Data10],[Data11],[Data12])) pvt

 
Select *  From (        Select [Code]              ,[Data]              ,[Col] = concat('Data',Row_Number() over (Partition By [Code] Order by 1/0))         From  #BidStatusCalculation       ) src Pivot (max([Data]) for [Col] in ([Data1],[Data2],[Data3],[Data4],[Data5],[Data6],[Data7],[Data8],[Data9],[Data10],[Data11],[Data12])) pvt​

and I am getting the below result

Code           Month1   Month2   Month3
GV Payroll      0        0        **3**

 
Code           Month1   Month2   Month3GV Payroll      0        0        **3** ​

The issue is that it is transposing the data from the bottom up meaning it takes the first record that it finds and makes it the last and I wanted it to be like below

Code           Month1   Month2   Month3
GV Payroll      **3**       0        0

 
Code           Month1   Month2   Month3GV Payroll      **3**       0        0​

What change should I make to the query?

Update I added the SortOrder Column as was suggested and the table is now shown below.

Code            Data    SortOrder
    SL Payroll       1     1
    GV Payroll       3     2
    Global Payroll   1     3
    TimeHCM          1     4
    SL Payroll       0      1
    GV Payroll       0      2
    Global Payroll  0      3
    TimeHCM          0     4
    SL Payroll       0     ....
    GV Payroll       0
    Global Payroll   0
    TimeHCM          0

 
Code            Data    SortOrder    SL Payroll       1     1    GV Payroll       3     2    Global Payroll   1     3    TimeHCM          1     4    SL Payroll       0      1    GV Payroll       0      2    Global Payroll  0      3    TimeHCM          0     4    SL Payroll       0     ....    GV Payroll       0    Global Payroll   0    TimeHCM          0​

Answer

This is your subquery:

    Select [Code], [Data],
           [Col] = concat('Data', Row_Number() over (Partition By [Code] Order by 1/0))
     From #BidStatusCalculation

 
    Select [Code], [Data],           [Col] = concat('Data', Row_Number() over (Partition By [Code] Order by 1/0))     From #BidStatusCalculation​

The order by 1/0 is a very strange construct. It is equivalent to: order by (select null). That is, there is no ordering.

Well, you have a problem. Tables, even temporary tables, represent unordered sets in SQL. If you are depending on some innate ordering of the table for your results, then you are out of luck. SQL doesn’t do that.

You need a column that specifies the ordering — presumably some sort of date column or id.

Advertisement

Answer