Assign group identifer to records with Cursor

• shogunSQL

  Say Hey Kid

  Points: 689

  More actions

  September 18, 2017 at 11:56 am

  #349132

  Hi,

  Can you please help with this?

  I have a table called HelpDesk with two fields(RowID and Name), And, I am trying to run a cursor to assign number, increment by 1, for every time it loops through the field Name and whenever it matches with value Firstname, it get a number. For example, for Rowid = 1, the grouid field should be 1, then it loops to rowid = 2, the grouid field should be 1, Same as rowid =3. When it gets to rowid =4, it matches the word FirstName in the name field, which gets a new number increment by 1, which is 2 for GroupID , and so forth. Is this sounds even possible?

  I tried this but it doesn't work for some reason

  declare GroupID_cursor Cursor for
select [name]
from [HelpDesk]
order by rowid asc
open groupid_cursor
fetch next from groupid_cursor into @name
　
While @@FETCH_STATUS = 0
begin
declare @counter int
set @counter = 1
set @name = @counter
update z set groupid = @name
from [HelpDesk] z
where [name] = 'Firstname'
set @counter = @counter + 1
fetch next from groupid_cursor into @name
end
close groupid_cursor
deallocate groupid_cursor

  

  Table and expected result

  create table HelpDesk
  (
  RowID int,
  Name varchar(20),
  GroupID varchar(20)
  )
  insert into HelpDesk
  (rowid,name,groupid)
  values
  (1,'Firstname','1'),
  (2,'Lasttname','1'),
  (3,'','1'),
  (4,'Firstname','2'),
  (5,'MiddleName','2'),
  (6,'Lastname','2'),
  (7,'Firstname','3'),
  (8,'','3'),
  (9,'','3'),
  (10,'Firstname','4'),
  (11,'','4'),
  (12,'','4'),
  (13,'','4')
  [/code]

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  September 18, 2017 at 12:28 pm

  #1959857

  Are you just designing this setup, or is it something that already exists and needs a good update?   I ask because while getting a group is possible, relying on such a query is not a good database design.  Every record should have the exact same set of fields, and it would be far better to straighten this out sooner rather than later.  Keeping each field as a separate record is a particularly bad idea.     Also, grouping of the nature you're looking for does NOT require a CURSOR.   Just a little out of the box thinking.

  Try this on for size:CREATE TABLE #HelpDesk (
    RowID int NOT NULL PRIMARY KEY CLUSTERED,
    Name varchar(20),
    GroupID varchar(20)
);

  INSERT INTO #HelpDesk (RowID, Name, GroupID)
    VALUES    (1, 'Firstname', NULL),
            (2, 'Lasttname', NULL),
            (3, '', NULL),
            (4, 'Firstname', NULL),
            (5, 'MiddleName', NULL),
            (6, 'Lastname', NULL),
            (7, 'Firstname', NULL),
            (8, '', NULL),
            (9, '', NULL),
            (10, 'Firstname', NULL),
            (11, '', NULL),
            (12, '', NULL),
            (13, '', NULL);

  WITH RANGES AS (

      SELECT RowID,
        LEAD(RowID, 1, 2147483647) OVER(ORDER BY RowID) - 1 AS GroupEnd,
        ROW_NUMBER() OVER(ORDER BY RowID) AS GroupID
    FROM #HelpDesk AS HD
    WHERE HD.Name = 'FirstName'
)
UPDATE HD
SET HD.GroupID = R.GroupID
FROM #HelpDesk AS HD
    LEFT OUTER JOIN RANGES AS R
        ON HD.RowID BETWEEN R.RowID AND R.GroupEnd

  SELECT *
FROM #HelpDesk;

  DROP TABLE #HelpDesk;

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• drew.allen

  SSC Guru

  Points: 76945

  More actions

  September 18, 2017 at 12:51 pm

  #1959860

  sgmunson - Monday, September 18, 2017 12:28 PM

  Are you just designing this setup, or is it something that already exists and needs a good update?   I ask because while getting a group is possible, relying on such a query is not a good database design.  Every record should have the exact same set of fields, and it would be far better to straighten this out sooner rather than later.  Keeping each field as a separate record is a particularly bad idea.     Also, grouping of the nature you're looking for does NOT require a CURSOR.   Just a little out of the box thinking.

  Try this on for size:CREATE TABLE #HelpDesk (
    RowID int NOT NULL PRIMARY KEY CLUSTERED,
    Name varchar(20),
    GroupID varchar(20)
);

  INSERT INTO #HelpDesk (RowID, Name, GroupID)
    VALUES    (1, 'Firstname', NULL),
            (2, 'Lasttname', NULL),
            (3, '', NULL),
            (4, 'Firstname', NULL),
            (5, 'MiddleName', NULL),
            (6, 'Lastname', NULL),
            (7, 'Firstname', NULL),
            (8, '', NULL),
            (9, '', NULL),
            (10, 'Firstname', NULL),
            (11, '', NULL),
            (12, '', NULL),
            (13, '', NULL);

  WITH RANGES AS (

      SELECT RowID,
        LEAD(RowID, 1, 2147483647) OVER(ORDER BY RowID) - 1 AS GroupEnd,
        ROW_NUMBER() OVER(ORDER BY RowID) AS GroupID
    FROM #HelpDesk AS HD
    WHERE HD.Name = 'FirstName'
)
UPDATE HD
SET HD.GroupID = R.GroupID
FROM #HelpDesk AS HD
    LEFT OUTER JOIN RANGES AS R
        ON HD.RowID BETWEEN R.RowID AND R.GroupEnd

  SELECT *
FROM #HelpDesk;

  DROP TABLE #HelpDesk;

  This requires two scans of the base table.  The following only requires one.
  ;

  WITH HelpDeskGroups AS
(
    SELECT *, COUNT(CASE WHEN [Name] = 'Firstname' THEN 1 END) OVER(ORDER BY RowID ROWS UNBOUNDED PRECEDING) AS Grp
    FROM #HelpDesk2
)
UPDATE HelpDeskGroups
SET GroupID = Grp


  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• shogunSQL

  Say Hey Kid

  Points: 689

  More actions

  September 18, 2017 at 12:54 pm

  #1959862

  sgmunson - Monday, September 18, 2017 12:28 PM

  Are you just designing this setup, or is it something that already exists and needs a good update?   I ask because while getting a group is possible, relying on such a query is not a good database design.  Every record should have the exact same set of fields, and it would be far better to straighten this out sooner rather than later.  Keeping each field as a separate record is a particularly bad idea.     Also, grouping of the nature you're looking for does NOT require a CURSOR.   Just a little out of the box thinking.

  Try this on for size:CREATE TABLE #HelpDesk (
    RowID int NOT NULL PRIMARY KEY CLUSTERED,
    Name varchar(20),
    GroupID varchar(20)
);

  INSERT INTO #HelpDesk (RowID, Name, GroupID)
    VALUES    (1, 'Firstname', NULL),
            (2, 'Lasttname', NULL),
            (3, '', NULL),
            (4, 'Firstname', NULL),
            (5, 'MiddleName', NULL),
            (6, 'Lastname', NULL),
            (7, 'Firstname', NULL),
            (8, '', NULL),
            (9, '', NULL),
            (10, 'Firstname', NULL),
            (11, '', NULL),
            (12, '', NULL),
            (13, '', NULL);

  WITH RANGES AS (

      SELECT RowID,
        LEAD(RowID, 1, 2147483647) OVER(ORDER BY RowID) - 1 AS GroupEnd,
        ROW_NUMBER() OVER(ORDER BY RowID) AS GroupID
    FROM #HelpDesk AS HD
    WHERE HD.Name = 'FirstName'
)
UPDATE HD
SET HD.GroupID = R.GroupID
FROM #HelpDesk AS HD
    LEFT OUTER JOIN RANGES AS R
        ON HD.RowID BETWEEN R.RowID AND R.GroupEnd

  SELECT *
FROM #HelpDesk;

  DROP TABLE #HelpDesk;

  This is AWESOME! Thank you so much! Your script is actually way much better than Cursor. Speed and clean codes. I never thought CTEs could loop through all records but it did hell of a job for Cursor. Thanks.

• shogunSQL

  Say Hey Kid

  Points: 689

  More actions

  September 18, 2017 at 1:06 pm

  #1959864

  drew.allen - Monday, September 18, 2017 12:51 PM

  sgmunson - Monday, September 18, 2017 12:28 PM

  Are you just designing this setup, or is it something that already exists and needs a good update?   I ask because while getting a group is possible, relying on such a query is not a good database design.  Every record should have the exact same set of fields, and it would be far better to straighten this out sooner rather than later.  Keeping each field as a separate record is a particularly bad idea.     Also, grouping of the nature you're looking for does NOT require a CURSOR.   Just a little out of the box thinking.

  Try this on for size:CREATE TABLE #HelpDesk (
    RowID int NOT NULL PRIMARY KEY CLUSTERED,
    Name varchar(20),
    GroupID varchar(20)
);

  INSERT INTO #HelpDesk (RowID, Name, GroupID)
    VALUES    (1, 'Firstname', NULL),
            (2, 'Lasttname', NULL),
            (3, '', NULL),
            (4, 'Firstname', NULL),
            (5, 'MiddleName', NULL),
            (6, 'Lastname', NULL),
            (7, 'Firstname', NULL),
            (8, '', NULL),
            (9, '', NULL),
            (10, 'Firstname', NULL),
            (11, '', NULL),
            (12, '', NULL),
            (13, '', NULL);

  WITH RANGES AS (

      SELECT RowID,
        LEAD(RowID, 1, 2147483647) OVER(ORDER BY RowID) - 1 AS GroupEnd,
        ROW_NUMBER() OVER(ORDER BY RowID) AS GroupID
    FROM #HelpDesk AS HD
    WHERE HD.Name = 'FirstName'
)
UPDATE HD
SET HD.GroupID = R.GroupID
FROM #HelpDesk AS HD
    LEFT OUTER JOIN RANGES AS R
        ON HD.RowID BETWEEN R.RowID AND R.GroupEnd

  SELECT *
FROM #HelpDesk;

  DROP TABLE #HelpDesk;

  This requires two scans of the base table.  The following only requires one.
  ;

  WITH HelpDeskGroups AS
(
    SELECT *, COUNT(CASE WHEN [Name] = 'Firstname' THEN 1 END) OVER(ORDER BY RowID ROWS UNBOUNDED PRECEDING) AS Grp
    FROM #HelpDesk2
)
UPDATE HelpDeskGroups
SET GroupID = Grp


  Drew

  Your script works and faster too. Very nice work. Thank you so much!

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  September 18, 2017 at 1:17 pm

  #1959866

  drew.allen - Monday, September 18, 2017 12:51 PM

  sgmunson - Monday, September 18, 2017 12:28 PM

  Are you just designing this setup, or is it something that already exists and needs a good update?   I ask because while getting a group is possible, relying on such a query is not a good database design.  Every record should have the exact same set of fields, and it would be far better to straighten this out sooner rather than later.  Keeping each field as a separate record is a particularly bad idea.     Also, grouping of the nature you're looking for does NOT require a CURSOR.   Just a little out of the box thinking.

  Try this on for size:CREATE TABLE #HelpDesk (
    RowID int NOT NULL PRIMARY KEY CLUSTERED,
    Name varchar(20),
    GroupID varchar(20)
);

  INSERT INTO #HelpDesk (RowID, Name, GroupID)
    VALUES    (1, 'Firstname', NULL),
            (2, 'Lasttname', NULL),
            (3, '', NULL),
            (4, 'Firstname', NULL),
            (5, 'MiddleName', NULL),
            (6, 'Lastname', NULL),
            (7, 'Firstname', NULL),
            (8, '', NULL),
            (9, '', NULL),
            (10, 'Firstname', NULL),
            (11, '', NULL),
            (12, '', NULL),
            (13, '', NULL);

  WITH RANGES AS (

      SELECT RowID,
        LEAD(RowID, 1, 2147483647) OVER(ORDER BY RowID) - 1 AS GroupEnd,
        ROW_NUMBER() OVER(ORDER BY RowID) AS GroupID
    FROM #HelpDesk AS HD
    WHERE HD.Name = 'FirstName'
)
UPDATE HD
SET HD.GroupID = R.GroupID
FROM #HelpDesk AS HD
    LEFT OUTER JOIN RANGES AS R
        ON HD.RowID BETWEEN R.RowID AND R.GroupEnd

  SELECT *
FROM #HelpDesk;

  DROP TABLE #HelpDesk;

  This requires two scans of the base table.  The following only requires one.
  ;

  WITH HelpDeskGroups AS
(
    SELECT *, COUNT(CASE WHEN [Name] = 'Firstname' THEN 1 END) OVER(ORDER BY RowID ROWS UNBOUNDED PRECEDING) AS Grp
    FROM #HelpDesk2
)
UPDATE HelpDeskGroups
SET GroupID = Grp


  Drew

  Nicely done, sir!   I've heard about the use of ROWS UNBOUNDED and just hadn't seen such a good, simple example.  Hopefully, I'll retain this example and be able to integrate it into my "bag of tricks", so to speak.   Thanks!

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• jcelko212 32090

  SSCrazy Eights

  Points: 9294

  More actions

  September 20, 2017 at 4:15 pm

  #1960287

  shogunSQL - Monday, September 18, 2017 11:56 AM

  >>I have a table called HelpDesk with two fields (row_id andsomething_name), I am trying to run a cursor to assign number,increment by 1, for every time it loops through the field [sic] [sic]something_name and whenever it matches with value first_name, it geta number. <<

  >> I have a table called HelpDesk with two fields (row_id and something_name), I am trying to run a cursor to assign number, increment by 1, for every time it loops through the field [sic] [sic] something_name and whenever it matches with value first_name, it get a number. <<

  the first problem is conceptual. Fields have a completely different meaning from columns in SQL. I also hope you know that we don’t use row numbers in RDBMS; that’s how you handle the magnetic tape in the 1950s. There’s also no such thing as a generic “nameâ€; it has to be the name of something in particular. Which are doing was possible in COBOL because COBOL records do have fields in a hierarchical structure so there’s always a context.

  Why did you use the old Sybase UPDATE.. FROM.. syntax? It has all kinds of problems and doesn’t work because of cardinality errors.

  >> For example, for row_id = 1, the group_id field [sic] should be 1, then it loops to row_id = 2, the group_id field [sic] should be 1, Same as row_id =3. When it gets to row_id =4, it matches the word FirstName in the name field [sic], which gets a new number increment by 1, which is 2 for group_id, and so forth. Is this sounds even possible? <<

  Did you know that by definition, a table must have a primary key? But you left it off!

  CREATE TABLE HelpDesk
(something_name VARCHAR(20) NOT NULL,
group_nbr INTEGER NOT NULL,
PRIMARY KEY (something_name, group_id)
);

  I’m going to make a guess since we don’t have a logical specification (you describe the physical scan of a magnetic tape). I’m going to guess the group can have a first middle and last name, in which case your design is really bad. You committed a design flaw called attribute splitting. It means attributes whichbelongs to one entity, say a person or user, have been split across either multiple rows or tables.

  CREATE TABLE HelpDesk
(first_name VARCHAR(10) NOT NULL PRIMARY KEY,
middle_name VARCHAR(10),
last_name VARCHAR(10));

  now the row_id and in group number is no longer needed. Users are identified by their first name, and if some of the other names are missing, you can use the null. That’s why we put it in the language.
I’m going to make a guess since we don’t have a logical specification (you describe the physical scan of a magnetic tape). I’m going to guess the group can have a first middle and last name, in which case your design is really bad. You committed a design flaw called attribute splitting. It means attributes which belongs to one entity, say a person or user, have been split across either multiple rows or tables.CREATE TABLE HelpDesk(first_name VARCHAR(10) NOT NULL PRIMARY KEY, middle_name VARCHAR(10), last_name VARCHAR(10));now the row_id and in group number is no longer needed. Users are identified by their first name, and if some of the other names are missing, you can use the null. That’s why we put it in the language.

  Please post DDL and follow ANSI/ISO standards when asking for help.