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

share|improve this question
add comment

3 Answers 3

up vote 1 down vote accepted

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;
share|improve this answer
    
Fantastic! Thank you so much! –  Vaccano Aug 28 '12 at 18:59
add comment
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)
share|improve this answer
    
As a extra note: The scary part about this solution is if you end up with more groups then will fit in 8000. –  Edward Dortland Aug 28 '12 at 4:45
    
Near as I can tell this just does a normal pivot. The results are not grouped by the second column. –  Vaccano Aug 28 '12 at 16:29
    
Re-reading your question and looking at Aaron's answer I now see that I completely miss understood your problem. Sorry about that! –  Edward Dortland Aug 29 '12 at 5:54
add comment

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.

share|improve this answer
add comment

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.