troubleshooting Question

Dynamic Crosstab Query - Replicate Transform

Avatar of c0ry98
c0ry98 asked on
Microsoft SQL Server 2005
5 Comments1 Solution948 ViewsLast Modified:
Hi all,

I have a query that I need to convert from Access97 to TSQL.  

I've written a simple count/group by select to get the data to this state but I can't figure out the next step.

Here's where I am:
Location        cover           hits
140 ML           A                  21
140 ML           B                  14
140 ML           C                  19
181 ML           A                  45
181 ML           C                  10
181 ML           D                  45

And where I need to end up
cover           Total            140 ML            181 ML     ....(needs to grow dynamically)
A                  66                 21                   45
B                  14                 14
C                  29                 19                    10
D                  45                                         45

I've looked at the Pivot function however I was unable to figure out how to support n columns with custom names. This code will be in a stored procedure so dynamic tsql will work too.

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 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 1 Answer and 5 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