Split the output rows in groups in SQL Server

Question

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

Answer 1

SELECT ID, CASE WHEN (ID = 1 OR ID = 6 OR ID = 11) THEN Name Else NULL END
FROM @Names

I know it is a stupid way of doing it.
But, it is much better to write it in this way for a limited set of rows (as per your example).

If you have more number of rows (than 15), please post.
I will try to see if I can derive a formula so that the results can be printed per your expectations.

Answer 2

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;

Answer 3

You could calculate the row number and the total number of rows in a subquery. The outer select can then choose based on those columns:

select 
    id,
    case 
        when id = 1 then name 
        when id = total/3+1 then name 
        when id = total*2/3+1 then name 
        else '' 
    end
from (
    select row_number() over (order by id) as nr,
        (select count(*) from @names) as total,
        *
    from @names
) sub