How to make this data into a pivot table

  • I am still earning after all these years and one of the things I can not get my mind wrapped around is proper use of a pivot table without an aggregate.

    As an example, here is the data I have:

    EmpRight1 EmpGroup1

    EmpRight1 EmpGroup2

    EmpRight1 Empgroup3

    EmpRight2 Empgroup2

    EmpRight2 EmpGroup3

    EmpRight3 EmpGroup1

    EmpRight3 EmpGroup3

    What I want is a table as such:

    EmpGroup1 EmpGroup2 EmpGroup3

    EmpRight1 X X X

    EmpRight2 X X

    EmpRight3 X X

    How do I accomplish this?

  • Welcome to SSC...

    Thanks for the data... helps a LOT. Here's my solution... I'm sure there are better ways, but this one works... Note I created all the tables needed and inserted the data in my answer... that's how to post to get the best help - just figure that people here are busy, so the more work you do setting up your question, the better and faster answers you will get (because they're tested).

    This article[/url] is pretty much required reading for new folks... it explains much better how to post a question that will get answers.

    CREATE TABLE Emp(EmpRight VARCHAR(10),

    EmpGroup VARCHAR(10));

    GO

    INSERT INTO Emp(EmpRight,EmpGroup)

    VALUES ('EmpRight1', 'EmpGroup1'),

    ('EmpRight1', 'EmpGroup2'),

    ('EmpRight1', 'Empgroup3'),

    ('EmpRight2', 'Empgroup2'),

    ('EmpRight2', 'EmpGroup3'),

    ('EmpRight3', 'EmpGroup1'),

    ('EmpRight3', 'EmpGroup3');

    SELECT EmpRight,

    CASE WHEN SUM(EmpGroup1)>0 THEN 'X' ELSE '' END AS Grp1,

    CASE WHEN SUM(EmpGroup2)>0 THEN 'X' ELSE '' END AS Grp2,

    CASE WHEN SUM(EmpGroup3)>0 THEN 'X' ELSE '' END AS Grp3

    FROM

    (SELECT EmpRight,

    CASE WHEN EmpGroup = 'EmpGroup1' THEN 1 ELSE 0 END AS EmpGroup1,

    CASE WHEN EmpGroup = 'EmpGroup2' THEN 1 ELSE 0 END AS EmpGroup2,

    CASE WHEN EmpGroup = 'EmpGroup3' THEN 1 ELSE 0 END AS EmpGroup3

    FROM Emp) x

    GROUP BY EmpRight;

  • maybe....

    CREATE TABLE #Emp(EmpRight VARCHAR(10),

    EmpGroup VARCHAR(10));

    GO

    INSERT INTO #Emp(EmpRight,EmpGroup)

    VALUES ('EmpRight1', 'EmpGroup1'),

    ('EmpRight1', 'EmpGroup2'),

    ('EmpRight1', 'Empgroup3'),

    ('EmpRight2', 'Empgroup2'),

    ('EmpRight2', 'EmpGroup3'),

    ('EmpRight3', 'EmpGroup1'),

    ('EmpRight3', 'EmpGroup3');

    SELECT EmpRight,

    ISNULL(MAX(CASE WHEN EmpGroup = 'EmpGroup1' THEN 'X' END),'') as EmpGroup1,

    ISNULL(MAX(CASE WHEN EmpGroup = 'EmpGroup2' THEN 'X' END),'') as EmpGroup2,

    ISNULL(MAX(CASE WHEN EmpGroup = 'EmpGroup3' THEN 'X' END),'') as EmpGroup3

    FROM #emp

    GROUP BY EmpRight

    DROP TABLE #emp

    EDIT for NULLS

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • By definition, an aggregate combines separate elements into one whole. This is exactly what you are doing with a pivot, which is why a pivot requires an aggregate. That being said, the aggregate can be trivial. Typically, people use MIN or MAX when they know that there is only one possible value within each group.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Thanks for the thoughtful replies!

    Now let me go beat my keyboard 🙂

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply