How to select halves of multiple groups in a single SQL Server query?

Question

I have a SQL Server 2008 table with records in multiple groups comprised of composite group IDs (i.e. GroupID = Col1 + Col2 + Col3) and need to randomly split each of those groups into control groups, reassigning the value for Col1 only, such that half of each group will remain in the original group and half will be placed into a new group. How can I do this in a single statement such that I do not have to go through each Col2+Col3 group manually?

In other words, I would like to do something like this:

UPDATE dbo.DM_Main
SET PkgPt1 = 'CD2'
WHERE ID IN (
    SELECT TOP 50 PERCENT ID
    FROM dbo.DM_Main
    WHERE PkgPt1 = 'CD1'
    GROUP BY PkgPt2, PkgPt3 -- obviously this line won't work
    ORDER BY NEWID()
)

score 3 · Accepted Answer · answered Sep 17 '13 at 19:57

Here is an idea. Enumerate the rows of each group. Then put the even (or odd) ones in the control group. You can do this in SQL Server using an updatable CTE:

with toupdate as (
      select m.*,
             row_number() over (partition by PkgPt2, PkgPt3 order by newid()) as seqnum
      from DM_Main m
     )
update toupdate
    set PkgPt1 = (case when seqnum % 2 = 0 then 'CD2' else PkgPT1 end);

How to select halves of multiple groups in a single SQL Server query?

1 Answers1