[2k] 다양한 Case문 활용 예

1. /**********************************************************************************************
   -- Title : [2k] 다양한 Case문 활용 예
   -- Reference : 웹페이지 검색
   -- Key word : Case
   **********************************************************************************************/
   The CASE statement is a very flexible tool. Here are just a few of the tricks you can work
   with it.
    
   Item translation
   One of the simplest things you can do is to derive a columns contents based on it뭩
   contents, or the contents of another data item.
    
   SELECT  pub_name,
           CASE WHEN state is NULL or state = ''
               THEN 'Not supplied'
               ELSE state
           END
   FROM publishers
   SELECT CASE WHEN datepart (dw, getdate()) In (1, 7)
               THEN 'Weekend overtime rates apply :-)'
               ELSE 'Standard rates apply'
   END
   Titling subtotals
   
   You can use CUBE and ROLLUP to produce totals and subtotals within a result set, but
   because SQL Server places NULLS for non-totaled columns in the total rows, they do not
   always look very pretty. In this example we use the CASE statement to place emphasis on
   the subtotal lines.
    
   SELECT   CASE GROUPING(stor_name)
                       WHEN 1 then '[State total]'
                       ELSE stor_name
                   End as store,
                   state,
                   sum(qty)
                   FROM stores s
   JOIN      sales sl on s.stor_id = sl.stor_id
   GROUP  BY stor_name, state
   WITH     CUBE
   HAVING GROUPING(state) = 0
   ORDER   BY state, GROUPING(stor_name)
    
   Simplifying Output
   This batch lists of sales by store, and gives a general indication of the stores
   performance against the average quantity of sales per store.
    
   declare @storeAvg int
   select @storeAvg = avg(storeTotal)
   from (
       select stor_id,
       sum(qty) as storeTotal
       from sales
       group by stor_id
   ) as derived
   
   select  stor_name,
           storeTotal,
   case    when storeTotal > @storeAvg * 1.1
               then 'Above average'
           when storeTotal < @storeAvg * 0.9
               then 'Below average'
           else 'About average'
   end as storePerformance
   from (
           select  stor_id,
                   sum(qty) as storeTotal
                   from     sales
                   group    by stor_id
   ) as derivedStores
   join stores
   on stores.stor_id = derivedStores.stor_id
    
   This query batch starts by calculating the average of total sales per store, which is stored
   in the variable @storeAvg, then that average is used to rate the stores sales performance.
   Grouping data into ranges
   This example calculates the number of orders in certain size ranges. It also shows off the
   power of SQL as a set-based language: to code this as in a procedural language like VB
   would not be able to do this in a single statement.
    
   SELECT    SUM (CASE when qty <= 5 then 1 else 0 end)
               as '1_to_5',
             SUM (CASE when qty between 6 and 10 then 1 else 0 end)
               as '6_to_10',
             SUM (CASE when qty between 11 and 20 then 1 else 0 end)
               as '11_to_20',
             SUM (CASE when qty > 20 then 1 else 0 end)
               as '20_plus'
   FROM sales
    
   Complex Sequencing
   CASE statements can be used in an ORDER BY clause to order non-sequential data.
   This example sorts stores by name, but puts all the stores in California at the top of the
   list.
    
   SELECT   stor_name, state
   FROM     stores
   ORDER    BY CASE state
                WHEN 'CA' then 1
                ELSE 2
            END,
            stor_name