Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
@AaronBertrand - yep, that is exactly what I want. (Unique combinations grouped by MyGroup). Here is my question that lead to this query: dba.stackexchange.com/questions/23232/… –  Vaccano Aug 28 '12 at 18:23

2 Answers 2

up vote 7 down vote accepted
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;
share|improve this answer
    
I bow down to you. –  Max Vernon Aug 28 '12 at 18:30
    
@Max entirely not necessary. This is an old trick and a FAQ. :-) –  Aaron Bertrand Aug 28 '12 at 18:31
    
why the FOR XML PATH? Just to reduce the subquery's output to a single item, thereby avoiding the Subquery returned more than 1 value error? –  Max Vernon Aug 28 '12 at 18:43
    
>Pop!< That was the sound of my poor little mind being blown out! I am going to go try to figure out what this is doing. Be back in an hour or two. –  Vaccano Aug 28 '12 at 18:43
    
@Max this allows you to perform concatenation, collapsing the values into a single string. –  Aaron Bertrand Aug 28 '12 at 18:46

(I've undeleted this to show how wrong I was about the question).

This should work as a single statement:


SELECT  grp.GroupValue
    , COUNT(DISTINCT (grp.GroupValue + grp.MyGroup)) AS 'Count'
FROM    MyTable tbl (NOLOCK)
INNER JOIN (
    SELECT COALESCE(MyGroup + ', ' 
           + CAST(MyValue AS VARCHAR(10)), CAST(MyValue AS VARCHAR(10))) AS GroupValue
        , MyGroup
    FROM MyTable
    ) grp ON tbl.MyGroup = grp.MyGroup
GROUP BY grp.GroupValue;

share|improve this answer
    
Hmmm, I don't see how that would give the same results. To be sure, I tried it. But I get "The multi-part identifier "tbl.MyGroup" could not be bound." –  Vaccano Aug 28 '12 at 18:25
    
yeah, I'm looking at resolving that right now.... –  Max Vernon Aug 28 '12 at 18:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.