Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2016
    Posts
    2

    Unanswered: understanding crosstab

    Hello,

    I've examined a few solutions to crosstab problem on the net, but I don't quite see how to solve the case below.

    Given the input table, my goal is to create the output table; the output table's first column has the names of all laborers, and the output table's last column lists the names of all managers.

    input table employees:

    | name | title |
    +---------+----------+
    | Javier | laborer |
    +---------+----------+
    | Mae | laborer |
    +---------+----------+
    | Pearl | manager |
    +---------+----------+
    | Grady | manager |
    +---------+----------+

    output:

    | laborer | manager |
    +-----------+-----------+
    | Javier | Grady |
    +-----------+-----------+
    | Mae | Pearl |
    +-----------+-----------+

    Code:
    create table employees (
        name varchar (20),
        title varchar (20)) ;
    
    insert into employees values ('Javier', 'laborer'),
                                 ('Mae', 'laborer'),
                                 ('Pearl', 'manager'),
                                 ('Grady', 'manager');
    The following query does not work. But, could a variant solve the issue.
    If "max/if" is used, can you please explain how it works.
    Thanks.

    Code:
    select 
        max(if (title='laborer', name, null)),
        max(if (title='manager', name, null))
    from employees;

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    472
    Provided Answers: 22
    That's not how Crosstab works. Crosstab is like a pivot table that sums the results, not stack a hierarchy.

  3. #3
    Join Date
    Aug 2016
    Posts
    2

    stack a hierarchy

    Quote Originally Posted by ranman256 View Post
    That's not how Crosstab works. Crosstab is like a pivot table that sums the results, not stack a hierarchy.
    So, could you explain a method to stack the hierarchy shown in the the output above. Are there more than one ways for doing this?

    Thx.

Tags for this Thread

Posting Permissions

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