We help IT Professionals succeed at work.

SQL pivot query 2

318 Views
Last Modified: 2014-05-15
Hi,

EE helped me with this question
https://www.experts-exchange.com/Database/MS-SQL-Server/SQL_Server_2008/Q_28434814.html#a40068498

I was wondering if there is a way to display the result based on the pre-defined range (sorted)

state Row_Count   Amount      pct
----- ----------- ----------- ---------
0-0.03     1           10          0.03
0.1-0.4    4           100         0.34
0.4-0.5    3           130         0.45

thx
Comment
Watch Question

Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
You can specify:
ORDER BY pct

Or you could include a table, hard-coded if needed, of the ranges with custom sort orders, join to that, then sort on the result.

Not sure specifically what you are looking for, since "0-0.03", etc., aren't shown in the original data.

Author

Commented:
Some of the results are based of the data itself, such as NY, NJ....

Some will need to be based of the predefined ranges....those are not in the data, is there a way around?  thanks
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
It sounds like you just need a range table to join to and use to determine range and sort.

But I don't know enough details on that to tell you how to do it.  What specifically are you trying to sort by?

Author

Commented:
ok, lets say I have a range table,   col range will need to be on the result, col category will be used for different report and col sort can be used to sort...will this work?   sorry if this is not clear enough  thx


range       Category     sort
----- ----------- ----------- ---------
0-0.5             state          1
0.5-1.0          state          2
>1.0               state          3
0-100             score          1
200-400         score          2
>400               score         3
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
thank you so very much, exactly what I need..thanks again
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.