디비 버젼에 따라 달라 지는데요 2005 이상이시면

질문의 내용을 보면  ceiling, row_number를 이용하시는게 더 맞을듯 하네여...

with res as (
 select 1 num   ,'L1' txt union all
 select 2       ,'L1' union all
 select 3       ,'L1' union all
 select 4       ,'L1' union all
 select 5       ,'L1' union all
 select 6       ,'L2' union all
 select 7       ,'L2' union all
 select 8       ,'L2' union all
 select 9       ,'L2' union all
 select 10      ,'L2' union all
 select 11      ,'L2' )
select *
     , ceiling( row_number() over(partition by txt order by txt, num ) / 3.) idx1  
     , txt + '-' + convert(varchar(2) ,   ceiling( row_number() over(partition by txt order by txt, num ) / 3.) ) TXT1
    
     , NTILE(2) over(partition by txt order by txt ,num ) idx2
     , txt + '-' + convert(varchar(2) , NTILE(2) over(partition by txt order by txt ,num ) )  TXT2
  from res
 

안녕하세요! SQL Server 2005 이상을 쓰시면 아래 쿼리를 활용하시길 바랍니다.

WITH TBL AS

(

SELECT 1 AS num, 'L1' as txt

UNION ALL SELECT 2, 'L1'

UNION ALL SELECT 3, 'L1'

UNION ALL SELECT 4, 'L1'

UNION ALL SELECT 5, 'L1'

UNION ALL SELECT 6, 'L2'

UNION ALL SELECT 7, 'L2'

UNION ALL SELECT 8, 'L2'

UNION ALL SELECT 9, 'L2'

UNION ALL SELECT 10, 'L2'

UNION ALL SELECT 11, 'L2'

UNION ALL SELECT 12, 'L2'

UNION ALL SELECT 13, 'L2'

UNION ALL SELECT 14, 'L3'

)

SELECT

*

, TXT + '-'+ CONVERT(VARCHAR,CEILING((ROW_NUMBER() OVER(PARTITION BY TXT ORDER BY TXT)-1)/3) + 1)

FROM TBL

CEILING(Transact-SQL): http://msdn.microsoft.com/ko-kr/library/ms189818.aspx