Sql-server – Cross Apply without a function

cross-applysql serversql-server-2008

I would like to run a query very similar to this on one of my production databases:

-- Setup the table (Already there in my real scenario)
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 (I can't make this in my production env)
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

But I don't have rights to create a User Defined Function on that database (I am a developer).

Is there a way to accomplish this query with out the use of a user defined function (or any other "Create" statement)?

Note: I tried doing a cross apply with the contents of my Function, but it failed (I don't think multi-statement expressions are allowed in the Cross Apply).

Best Answer

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(''), TYPE).value('.[1]','nvarchar(max)', 1, 2, '')
  FROM dbo.MyTable AS t
) AS x 
GROUP BY Val;
Related Question