Using the fancy group by and rollup functions with a combination of unpivot and pivot makes this a bit simpler. This is run on John's sample table generated above.
Note that in the final select, I sum the columns coming out of the pivot, even though this isn't necessary for the results - it is though required to allow me to use the group by rollup option.
Also note that this solution is only valid for 2008 onwards.
John's point about this being a pain to manage is spot on. It could be made dynamic with a bit of work though.
Edit: Here's a dynamic version. As requested, it doesn't rely on specific values in the status fields. It does though force URL columns to display to the right of non-URL columns. Within the two column groups, columns will be alpha sorted left to right by name. It's simple enough to take this out if you don't need it.
There are fancy functions like group by rollup and things that might can do this for you, however I am not a heavy TSQL developer type guy. Going back old school how I solved for this many years ago was doing a UNION with sums. This code should get you the end result but there may be more proper ways or efficient ways of doing the same thing.
answered Mar 14, 2015 at 02:52 PM
I'm guessing there is a much more elegant solution to this, however, I did it in this fashion:
One of the beauties of T-SQL is there is always another way to get things done. ;-) This solution, however, is NOT dynamic so if you have additional columns or status values, this type of coding will become very ugly very quickly I would imagine. I could also be way off base in this solution.
If there is a better way, please someone post so that I can learn too!
Hope that helps some!
answered Mar 15, 2015 at 04:04 PM
I have a similar question posted in a separate thread. Below is the link. All i have added is team name. How do i get the sum or a count by team wise.
answered Apr 06, 2015 at 12:03 PM