Results 1 to 5 of 5

Thread: PIVOT - Help

  1. #1
    Join Date
    Sep 2011
    Posts
    44

    Unanswered: PIVOT - Help

    Hi there, I havent used the PIVOT function before.

    I have the following table

    id Row Dept
    001 1 aaa
    001 2 bbb
    001 3 ccc
    001 4 ddd
    002 1 aaa
    002 2 ccc

    I would like it in the following format

    id Dept1 Dept2 Dept3 Dept4
    001 aaa bbb ccc ddd
    002 aaa ccc

    Is this possible?

    Thanks in advance for any help

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What happens when you get a 5th, 6th, ..., Nth department for a given id?
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2011
    Posts
    44
    There will be a max of 4 departments per id.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT id
         , Max(CASE WHEN row = 1 THEN dept END) As dept1
         , Max(CASE WHEN row = 2 THEN dept END) As dept4
         , Max(CASE WHEN row = 3 THEN dept END) As dept3
         , Max(CASE WHEN row = 4 THEN dept END) As dept4
    FROM   your_table
    GROUP
        BY id
    Something like this?
    George
    Home | Blog

  5. #5
    Join Date
    Sep 2011
    Posts
    44
    That looks good. I'll give this a try.

    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
  •