Split output strings in groups in SQL Server

Question

I need to split the lines the same way, so here, for example, there are 15 lines. I want to share equally what is in the three groups, but I want the name to appear only before the first record 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' 

Output Required:

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

Answer 1

If you are using SQL 2005 or higher, 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 the following:

 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 2

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

I know this is a dumb way to do this.
But it is much better to write it this way for a limited set of strings (as per your example).

If you have more lines (than 15), send a message.
I will try to see if I can get the formula so that the results can be printed according to your expectations.

Answer 3

You can calculate the line number and the total number of lines in the subquery. Then the external selection can select based on these 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