Generate Sequence number in column

1st Way

alter table Example

add NewColumn int identity(1,1)

 
2nd Way

DECLARE @id INT

SET @id = 0

UPDATE accounts2

SET @id = id = @id + 1

GO

  

Comma Separated string into individual rows

SELECT A.[State], 

     Split.a.value('.', 'VARCHAR(100)') AS String 

 FROM  (SELECT [State], 

         CAST ('<M>' + REPLACE([City], ',', '</M><M>') + '</M>' AS XML) AS String 

     FROM  TableA) AS A CROSS APPLY String.nodes ('/M') AS Split(a);