June 23, 2016 at 11:45 am
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?
June 23, 2016 at 12:25 pm
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;
June 23, 2016 at 12:34 pm
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
June 23, 2016 at 12:41 pm
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
June 23, 2016 at 1:24 pm
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