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;
|improve this answer|||||
DECLARE @columns VARCHAR(8000)

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

DECLARE @query VARCHAR(8000)

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

|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

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

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

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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