# How to convert query results into groups of common results

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.)

Seems a simpler approach:

``````SELECT GroupValue = Val, [Count] = COUNT(DISTINCT MyGroup) FROM
(
SELECT MyGroup, Val = STUFF((SELECT ', ' + RTRIM(MyValue)
FROM dbo.MyTable WHERE MyGroup = t.MyGroup FOR XML PATH('')), 1, 2, '')
FROM dbo.MyTable AS t
) AS x GROUP BY Val;
``````
``````DECLARE @columns VARCHAR(8000)

SELECT @columns = COALESCE(@columns + ',[' + Mygroup + ']',
'[' + Mygroup+ ']')
FROM MyTable
GROUP BY Mygroup

DECLARE @query VARCHAR(8000)

SET @query = '
SELECT *
FROM (  select MyGroup,
MyValue,
Row_number() over ( partition by Mygroup
Order by Mygroup) as rownum
from Mytable) a
PIVOT
(
MAX(MyValue)
FOR [MyGroup]
IN (' + @columns + ')
)
AS p'

EXECUTE(@query)
``````
• As a extra note: The scary part about this solution is if you end up with more groups then will fit in 8000. Aug 28, 2012 at 4:45
• Near as I can tell this just does a normal pivot. The results are not grouped by the second column. Aug 28, 2012 at 16:29

This is about what I was looking for:

``````-- Setup the table
create table dbo.MyTable(MyValue int, MyGroup varchar(5))
insert into dbo.MyTable values
(56,'I'), (12,'I'), (56, 'II'), (12, 'II'), (56, 'III'), (56, 'IV'), (56, 'V'), (12, 'V')

-- Create a function to cross apply with
create function dbo.GetOrderGroup(@groupName varchar(15))
returns @ReturnTable table(GroupValue varchar(8000), GroupName varchar(15))
as
begin
DECLARE @groupValue VARCHAR(8000)

SELECT @groupValue = COALESCE(@groupValue + ', '
+ cast(MyValue as VARCHAR(10)), cast(MyValue as VARCHAR(10)))
FROM MyTable
where MyGroup = @groupName

insert into @ReturnTable(GroupValue, GroupName)
SELECT @groupValue, @groupName
return;
end;
go

-- cross apply and get the distinct values
SELECT  grp.GroupValue, count(distinct (grp.GroupValue + grp.GroupName)) as 'Count'
from    MyTable tbl (NOLOCK)
cross apply dbo.GetOrderGroup(tbl.MyGroup) grp
GROUP BY grp.GroupValue
``````

It returns:

``````   GroupValue         Count
_________________|______________
12, 56       |      3
_________________|______________
56         |      2
_________________|______________
``````

Which is close enough to what I needed.