Sql – Split the output rows in groups in SQL Server

sqlsql-server

I have to divide the rows equally,
so here, for example, there are 15 rows. I want to divide equally, which is in three groups, but I want the name to come only in front of the first entry of each group, as shown:

DECLARE @NAMES TABLE
(
[ID] INT IDENTITY,
[NAME] VARCHAR(20)
)


INSERT INTO @NAMES
SELECT 'NAME1' UNION ALL
SELECT 'NAME2' UNION ALL
SELECT 'NAME3' UNION ALL
SELECT 'NAME4' UNION ALL
SELECT 'NAME5' UNION ALL
SELECT 'NAME6' UNION ALL
SELECT 'NAME7' UNION ALL
SELECT 'NAME8' UNION ALL
SELECT 'NAME9' UNION ALL
SELECT 'NAME10' UNION ALL
SELECT 'NAME11' UNION ALL
SELECT 'NAME12' UNION ALL
SELECT 'NAME13' UNION ALL
SELECT 'NAME14' UNION ALL
SELECT 'NAME15'

Desired Output:

ID          NAME
----------- --------------------
1           NAME1
2           
3           
4           
5           
6           NAME6
7           
8           
9           
10          
11          NAME11
12          
13          
14    
15

Best Solution

If you are using SQL 2005 or above, the following should do the job for any number of rows:

declare @numBuckets;
select @numBuckets = 3;

;with nameBase as
(
    select  ntile(@numBuckets) over(order by ID) as bucket,
            NAME, ID
    from    @NAMES
),
nameRows as
(
    select  row_number() over(partition by bucket order by ID) as rn,
            NAME, ID
    from    nameBase

)
select  n.ID, case when rn = 1 then n.NAME else null end as NAME
from    nameRows n
order by ID;

If you want a solution for SQL 2000 or ANSI, try this:

declare @numRecs int, @numBuckets int, @recsPerBucket int;
select @numRecs = count(*) from @NAMES;
select @numBuckets = 3;
select @recsPerBucket = @numRecs / @numBuckets;

select  n.ID, case when d1.minIdInBucket is null then null else n.NAME end as NAME
from    @NAMES n
left join (
            select  min(n2.ID) as minIdInBucket
            from    (
                        select  n1.ID, n1.NAME,
                                (
                                    select  count(*) / @recsPerBucket
                                    from    @NAMES n2
                                    where   n2.ID < n1.ID
                                ) as bucket
                        from    @NAMES n1
                    ) n2
            group by n2.bucket
        ) d1
on      n.ID = d1.minIdInBucket
order by n.ID;
Related Question