Oracle SQL summary/pivot table

Question

I have a data set that looks like this:

    ID      Description                     Team
    A       Missing Name; Missing Gender    Team1
    B       Missing Gender                  Team1
    C       Missing Name                    Team2
    D       Missing Name                    Team2

i.e. each ID has one line, with a text column (description) which identifies any issues with the ID, as well as the name of the team that the ID belongs to. I've managed to summarise it like this:

    Team        Missing Gender      Missing Name
    Team1       2                   1
    Team2       0                   2

By using a case statement that looks like this:

       SELECT
    "Team",
    sum(case when "DQ Info" like '%Missing Gender%' then 1 ELSE 0 END) as "Missing Gender",
    sum(case when "DQ Info" like '%Missing Name%' then 1 ELSE 0 END) as "Missing Name"
    FROM
    (*)
    GROUP BY "Team"

I'd like to be able to summarise this data so that it looks like this instead:

    Description         Team1       Team2
    Missing Gender      2           0
    Missing Name        1           2

I know there must be a way of doing it, but I'm struggling at the moment - just using "Description" instead of "Team" in the above field gives a result like this:

    Description                     Team1       Team2
    Missing Name; Missing Gender    1           0
    Missing Gender                  1           0
    Missing Name                    0           2

which isn't quite what I'm after. Any assistance is appreciated, though I'll keep tinkering away so if I manage to figure it out I will update this post. Note this is just an example of the dataset, rather than actually what it is.

Edit: Query below as per bpgergo's suggestion. Having done some reading, it looks like the below will short circuit i.e. terminate the query once the first criteria in the inner case is met, which means it'll never be able to count a column more than once. It's a shame, but I think I'll have to stick with my original query.

    SELECT
    "Description",
    sum(case when "Team" like 'Team1' then 1 ELSE 0 END) as "Team1",
    sum(case when "Team" like 'Team2' then 1 ELSE 0 END) as "Team2"
    FROM(SELECT
    "Team",
    case when "DQ Info" like '%[Check Role]%' then '[Check Role]'
    ELSE case when "DQ Info" like '%[Client deceased]%' then '[Client deceased]'
    ELSE case when "DQ Info" like '%[2 Parents not recorded]%' then '[2 Parents not recorded]'
    ELSE case when "DQ Info" like '%[Religion not recorded]%' then '[Religion not recorded]'
    ELSE case when "DQ Info" like '%[1st Language not recorded]%' then '[1st Language not recorded]'
    ELSE case when "DQ Info" like '%[Ethnicity not recorded]%' then '[Ethnicity not recorded]'
    ELSE case when "DQ Info" like '%[No Current Worker]%' then '[No Current Worker]'
    ELSE case when "DQ Info" like '%[No Current Team]%' then '[No Current Team]'
    ELSE case when "DQ Info" like '%[Update Gender]%' then '[Update Gender]'
    ELSE case when "DQ Info" like '%[No Plan]%' then '[No Plan]'
    ELSE case when "DQ Info" like '%[Update Plan]%' then '[Update Plan]'
    END END END END END END END END END END END
     as "Description"
    FROM(
    *
    FROM
    (*)WORKLOAD)DATA)
    GROUP BY "Description"

score 0 · Answer 1 · answered Sep 10 '12 at 09:56

You're halfway there. Try something like this as an inner select

select 
case when "Description" like '%Missing Gender%' then "Missing_gender" 
ELSE case when "Description" like '%Missing Name%' then "Missing_name" ELSE null END
END as "Description",
Team
from your_table

The whole select would be something like this

SELECT
"Description",
sum(case when "Team" = 'Team1' then 1 ELSE 0 END) as "Team1",
sum(case when "Team" = 'Team2' then 1 ELSE 0 END) as "Team2",
FROM
(
  select 
  case when "Description" like '%Missing Gender%' then "Missing_gender" 
  ELSE case when "Description" like '%Missing Name%' then "Missing_name" ELSE null END
  END as "Description",
  Team
  from your_table
)
GROUP BY "Description"

Thanks - this makes sense. However, when I run the query, it's not returning all of the "description" categories, nor are the counts quite right. I think that when using the case on the inner query, it's only identifying the first error in the "Description" - so if an ID has 2 errors, the above query would only identify the first error on that query (hopefully that makes sense!). I've posted an abbreviated version of the query above. — bawpie, Sep 10 '12 at 11:07

Oracle SQL summary/pivot table

1 Answers1