Loading ...
Sorry, an error occurred while loading the content.

Re: Cross Tab Help required...urgently

Expand Messages
  • peternilsson42
    ... with Person_Projects as ( select 1 PersonID, 12 ProjectID from dual union all select 1 PersonID, 15 ProjectID from dual union all select 2 PersonID, 12
    Message 1 of 5 , Sep 2, 2008
    View Source
    • 0 Attachment
      "scottfisheruk" <scott.fisher@...> wrote:
      >
      > I have a table with the following structure:
      >
      > PersonID ProjectID
      > 1 12
      > 1 15
      > 2 12
      > 3 12
      > 3 15
      >
      > I need to do a sort of cross tab query that would return:
      >
      > PersonID Proj12 Proj15 Proj16
      > 1 1 1 0
      > 2 1 0 0
      > 3 1 1 0

      with
      Person_Projects as
      (
      select 1 PersonID, 12 ProjectID from dual union all
      select 1 PersonID, 15 ProjectID from dual union all
      select 2 PersonID, 12 ProjectID from dual union all
      select 3 PersonID, 12 ProjectID from dual union all
      select 3 PersonID, 15 ProjectID from dual
      )
      select PersonID,
      sum(case when ProjectID = 12 then 1 else 0 end) Proj12,
      sum(case when ProjectID = 15 then 1 else 0 end) Proj15,
      sum(case when ProjectID = 16 then 1 else 0 end) Proj16
      from Person_Projects
      group by PersonID
      order by PersonID;

      --
      Peter
    Your message has been successfully submitted and would be delivered to recipients shortly.