troubleshooting Question

Pivot a table with 3 coulmns with two layers of header columns

Avatar of LuckyLucks
LuckyLucks asked on
Microsoft OfficeMicrosoft AccessMicrosoft Excel
3 Comments2 Solutions126 ViewsLast Modified:
Hi:

I have a table with 3 columns as below:


ENTITY ID      GROUP_NAME      SCORE_NAME
A-001            G1            GOOD
A-001         G2            BAD
A-002            G3            VERY GOOD
A-003            G1            GOOD
B-001            G4            BAD

I would like my excel data to look like:
      


                        G1        G2                G3            G4
ENTITY_ID     VG G B   VG G B   VG G B  VG G B
A-001               1                      1
A-002                                             1
A-003                 1
B-001                                                            1

I have figured out how to pivot on the entity_id and the group_name, see below, but also need the SCORE_NAME somehow with its score (=existential count).


TRANSFORM Count(GROUP_NAME) AS CountOfNAME
SELECT ENTITY_ID
FROM myTable
GROUP BY ENTITY_ID
PIVOT GROUP_NAME;
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros