-1

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

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

and I am getting the below result

Code           Month1   Month2   Month3
GV 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

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
halfer
  • 19,824
  • 17
  • 99
  • 186
SP1
  • 1,182
  • 3
  • 22
  • 47
  • Is there nothing else you can order by that is a **true** order in your window function? Based on the table, there isn't a way to guarantee the order of the resulting pivot. – S3S Jan 23 '19 at 21:01
  • I can't see anything that determines the order of your data in your table; how do we get the needed order with an `ORDER BY` clause? – Thom A Jan 23 '19 at 21:02
  • The format of code will always be the same which is SL Payroll, GV Payroll, Global Payroll, TimeHCM, and repeat – SP1 Jan 23 '19 at 21:05
  • But how do you know that Month1 is 3 and Month2 and Month3 are 0? There is nothing in the data you provided to indicate an order. Keep in mind that a table by definition is an unordered set. In other words you MUST be able to provide order via some means or this will never work correctly. – Sean Lange Jan 23 '19 at 21:08
  • And of course if possible it would be better if you could use columns instead of rows in an EAV type of pattern but sometimes that kind of thing isn't possible. If that isn't possible you need at least one more column to indicate which "group" of rows each row belongs to. – Sean Lange Jan 23 '19 at 21:10
  • I know because that's how I insert it into the temp table while looping through some dates...Are you saying the order in which I insert them is irrelevant. – SP1 Jan 23 '19 at 21:14
  • I can add a sort column and make it 1,2,3,4 for the 4 products but can you suggest how would I change the query then. – SP1 Jan 23 '19 at 21:14
  • 1
    Yes I am saying that. Add an identity or something you can use to sort them. If you have the order ability then this is pretty simple. But having columns for each group instead of rows would be about a million times better. – Sean Lange Jan 23 '19 at 21:16
  • 1
    See why [your data isn't ordered](https://web.archive.org/web/20160808070425/https://blogs.msdn.microsoft.com/conor_cunningham_msft/2008/08/27/no-seatbelt-expecting-order-without-order-by/) if you don't use an `order by`. Also, from [Aaron Bertrand in this answer](https://stackoverflow.com/questions/19982804/determine-latest-row-added-when-no-index-is-present/19983752#19983752). – S3S Jan 23 '19 at 21:28
  • 1
    @scsimon brilliant!!! I have been linking to that Conor Cunningham article for years. Just a few days it was deleted from MS. He told me he would try to get it reinstated. Never thought of using the wayback machine. You win the internet today!!! – Sean Lange Jan 23 '19 at 21:30
  • 1
    haha thanks @SeanLange and looking for it led me to Aaron's answer which is even quicker. Not sure why they deleted it... i bet the google analytics on how many time's it's been linked is through the roof. – S3S Jan 23 '19 at 21:31
  • I added the sortorder column but when I add it to the query I get this error The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. – SP1 Jan 23 '19 at 21:35
  • 1
    How about posting some code instead of "i added Order By"? I would prefer to do this kind of thing using conditional aggregation instead of pivot in the first place. Post up some consumable data and I will be happy to show you how it works. – Sean Lange Jan 23 '19 at 21:37
  • Hi Sean..sorry for not being clear..I updated the query to take into account the sort order Select * From ( Select [Code] ,[Data] ,[Col] = concat('Data',Row_Number() over (Partition By [Code] Order by 1/0)) From #BidStatusCalculation Order By SortOrder ) src Pivot (max([Data]) for [Col] in ([Data1],[Data2],[Data3],[Data4],[Data5],[Data6],[Data7],[Data8],[Data9],[Data10],[Data11],[Data12])) pvt – SP1 Jan 23 '19 at 21:39
  • That comment didn't change the order by in the window function... how did it take into account the order? It's the same as the query in your OP. Adding a column with a value to use as the sort order is fine but you need to use this as the `order by` column... `partition by [Code] order by [SortOrder]` – S3S Jan 23 '19 at 21:41
  • You have more than enough rep around here to know better than just splattering code in comments. Give us some details to help you or you are on your own. – Sean Lange Jan 23 '19 at 21:42
  • Yeah I apologize Sean..guess I am just little worked up today..I ll take a break and try to figure it out and then post the answer. – SP1 Jan 23 '19 at 21:44
  • 1
    I'm not asking you to post an answer, I am asking you to post a complete question. I will be happy to help you find an answer if there is a complete question. – Sean Lange Jan 23 '19 at 21:46
  • 1
    Change this`,[Col] = concat('Data',Row_Number() over (Partition By [Code] Order by 1/0))` to this `,[Col] = concat('Data',Row_Number() over (Partition By [Code] Order by [SortOrder]))` once you *actually* create that column in your table. – S3S Jan 23 '19 at 21:57
  • Thanks scsimon ..This is exactly what i figured out..It's working fine now..Thank you so much guys for clearing out my concepts and helping out..You guys are awesome and I really mean it :) – SP1 Jan 23 '19 at 22:00

1 Answers1

1

This is your subquery:

    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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Hi Gordon..Yeah other suggested that in the above comments and it's working fine now. Thanks for your reply. – SP1 Jan 23 '19 at 23:23