Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    20

    Question Unanswered: multiple metrics from one

    Hello. I have a little issue of trying to find the most efficient way do doing something.
    Consider a table
    mat age qty
    aaa 5 100
    aaa 7 300
    aaa 12 400
    aaa 3 700

    What I want to get out is
    Mat 0-4 5-9 10-14 15-19
    aaa 700 400 400 0

    I know I could do it with 4 views all joined but I hope I can use a CASE somehow ?

    Thanks for you help

    Gerry

  2. #2
    Join Date
    Sep 2003
    Posts
    522
    Code:
    declare @tbl table (mat char(3) not null, age int not null, qty int not null)
    
    insert @tbl values ('aaa', 5, 100)
    insert @tbl values ('aaa', 7, 300)
    insert @tbl values ('aaa', 12, 400)
    insert @tbl values ('aaa', 3, 700)
    
    select Mat, [0-4]=max([0]), [5-9]=max([5]), [10-14]=max([10]), [15-19]=max([15]) from (
       select Mat,
          [0]=case when age between 0 and 4 then qty else 0 end,
          [5]=case when age between 5 and 9 then qty else 0 end,
          [10]=case when age between 10 and 14 then qty else 0 end,
          [15]=case when age between 15 and 19 then qty else 0 end
       from @tbl
    ) x
    group by Mat

  3. #3
    Join Date
    Mar 2004
    Posts
    20
    PERFECT

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •