Generating Sequence number based on the column value

Question

I want to generate a sequence number based on the column value. I want to have this kind of output. I gonna use this in c# .net winform as GridView output

TABLE1

ID        Name      NoStub
 1        arte        3
 2       gonzake      2


TABLE2

ID      Name        StubNumberStart          StubNumberEnd
 1      arte           0001                      0003
 2      gonzake        0004                      0005

You can find answers here in http://stackoverflow.com/questions/7753080/select-row-n-times-with-sql http://stackoverflow.com/questions/1209395/how-to-retrieve-rows-multiple-times-in-sql-server http://stackoverflow.com/questions/2472662/make-sql-select-same-row-multiple-times http://stackoverflow.com/questions/6608055/selecting-the-same-row-multiple-times — Md. Parvez Alam, Jul 18 '13 at 04:06

score 0 · Answer 1 · edited Jul 18 '13 at 03:41

Try this query.. it will give result from Table 2 to Table 1

DECLARE @T1 AS TABLE (ID INT, NAME VARCHAR(50), STUBNUMBER VARCHAR(10))
INSERT INTO @T1 VALUES ( 1, 'ARTE', '001')
INSERT INTO @T1 VALUES ( 1, 'ARTE', '002')
INSERT INTO @T1 VALUES ( 1, 'ARTE', '003')

INSERT INTO @T1 VALUES ( 1, 'GONZAKE', '004')
INSERT INTO @T1 VALUES ( 1, 'GONZAKE', '005')



SELECT * FROM @T1

SELECT DISTINCT ID ,NAME, COUNT(*) AS NOSTUB FROM @T1
GROUP BY ID, NAME

If your request is different from Table 1 to Table 2 then please let me know .. you will get new query...

score 0 · Answer 2 · answered Jul 18 '13 at 05:20

ALTER PROCEDURE ExpandIt
AS
BEGIN
SET NOCOUNT ON;

DECLARE @Id int;
DECLARE @name varchar(50);
DECLARE @noStub int;

DECLARE @stubNumber char(8);
DECLARE @count as int = 0;
DECLARE @continuedID as int = 0;

DECLARE t1 CURSOR FAST_FORWARD FOR 
SELECT ID, Name,NoStub from Table1

OPEN t1 
FETCH NEXT FROM t1 INTO @Id, @name, @noStub

WHILE @@FETCH_STATUS = 0
BEGIN
  WHILE (@count < @noStub)
  BEGIN
    SET @count = @count + 1;
    SET @stubNumber = ('0000' + CONVERT (CHAR, @continuedID + @count));
    SET @stubNumber = SUBSTRING (@stubNumber,LEN(@stubNumber)-4+1, 4);

    INSERT INTO Table2 (ID, Name, StubNumber)
    VALUES (@Id, @name,@stubNumber); 
  END
  SET @continuedID = @count;
  SET @count = 0;
  FETCH NEXT FROM t1 INTO @Id, @name, @noStub
END

CLOSE t1 ;
DEALLOCATE t1
END

Generating Sequence number based on the column value

2 Answers2