I have a query that returns results like this:
MyValue MyGroup _______________|_______________ 56 | I _______________|_______________ 12 | I _______________|_______________ 56 | II _______________|_______________ 12 | II _______________|_______________ 56 | III _______________|_______________ 56 | IV _______________|_______________ 56 | V _______________|_______________ 12 | V _______________|_______________
I need to convert that into something like this:
ColA ColB ColC ColD Count _______________|_______________|_______________|_______________|_______________ 12 | 56 null null 3 _______________|_______________|_______________|_______________|_______________ 56 | null null null 2 _______________|_______________|_______________|_______________|_______________
I know I could setup an iterator and go over all the rows of my table and construct this result.
But I think there is another, "set based" way. Maybe a "Cross Apply" or "Pivot".
But it does not matter how many times I read the docs or look at examples for those, I can't seem to wrap my head around them enough to write my own.
Does anyone have a suggestion?
NOTE: In case it matters, assume that my query to get the first results looks like this:
select MyValue, MyGroup from SomeTable
(It is actually a complex set of CTE's but I can just add another layer if need be.)