dimanche 24 mai 2015

Different order by clause for different columns

I hope my title made sense, here's a sample of my table.

My table has 1 column for a unique product barcode, and 4 date columns which is for storing what date and time a product passed an inspection area(4 of them), i removed the date in my example so just assume they're all the same dates.

Product | Time1 | Time2 | Time3 | Time4
---------------------------------------
   A    | 10:00 | 10:15 | 10:30 | 10:45
   B    | 10:05 | 10:25 | 10:35 | 10:50
   C    | 10:10 | 10:20 | 10:40 | 10:55

Output:

 Inspect1 | Inspect2 | Inspect3 | Inspect4
 -----------------------------------------
    A            A         A          A
    B            C         B          B
    C            B         C          C

In my output, I want to select the product multiple times with different order by(order by Time1, order by Time2, etc.)

I used case select to select the product multiple times, how do I put an order by so that I get something like my example above.

SELECT     
CASE WHEN time1 BETWEEN '10:00' AND '11:00' THEN product END AS inspect1, 
CASE WHEN time2 BETWEEN '10:00' AND '11:00' THEN product END AS inspect2, 
CASE WHEN time3 BETWEEN '10:00' AND '11:00' THEN product END AS inspect3, 
CASE WHEN time4 BETWEEN '10:00' AND '11:00' THEN product END AS inspect4
FROM         Table

Aucun commentaire:

Enregistrer un commentaire