How to numerate (1,2,3...) my DataReader result

Question

so I have this code

               Using FileObject As New FileStream(FileName, FileMode.OpenOrCreate, FileAccess.Write, FileShare.None)
                    Using StreamWriterObj As New StreamWriter(FileObject)
                        connect.Open()
                        Using reader As SqlDataReader = command.ExecuteReader()
                            Dim FieldCount As Integer = reader.FieldCount - 1
                            Do While reader.Read()
                                StreamWriterObj.Write(reader.Item(0))
                                For i = 1 To FieldCount
                                    StreamWriterObj.Write(" @ ")
                                    StreamWriterObj.Write(reader.Item(i))
                                Next
                                StreamWriterObj.WriteLine()
                            Loop
                        End Using
                        connect.Close()
                    End Using
                End Using

which basically sets my results from an SQL query into a StreamWriter Object and exports it as a .txt file. Here's an example of the result after the select query:

+---------------------------------------+
|                Results                |
+---------------------------------------+
+                                       +
| Document No.|   #   |  col 3 |  col 4 |
+-------------+-------+--------+--------+
| 333456      |   0   | "value"| "value"|
+-------------+-------+--------+--------+
| 333456      |   0   | "value"| "value"|
+-------------+-------+--------+--------+
| 462345      |   0   | "value"| "value"|
+-------------+-------+--------+--------+
| 585357      |   0   | "value"| "value"|
+-------------+-------+--------+--------+

So my task is to make it in such a way that when i export to the .txt file, inside it will look like this:

+---------------------------------------+
|                Results                |
+---------------------------------------+
+                                       +
| Document No.|   #   |  col 3 |  col 4 |
+-------------+-------+--------+--------+
| 333456      |   1   | "value"| "value"|
+-------------+-------+--------+--------+
| 333456      |   2   | "value"| "value"|
+-------------+-------+--------+--------+
| 333456      |   3   | "value"| "value"|
+-------------+-------+--------+--------+
| 462345      |   1   | "value"| "value"|
+-------------+-------+--------+--------+
| 585357      |   1   | "value"| "value"|
+-------------+-------+--------+--------+
| 585357      |   2   | "value"| "value"|
+-------------+-------+--------+--------+

or in other words to numerate (next number) the rows based on the document no. (The select query is ordered by Doc. No).

I think a wise solution would be to use a StringBuilder but Im going to need some help with this.

Thank you

Answer 1

id do it in the actual sql with :-

WITH
  q AS
(select query)
,
sequenced as (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY [Document no. Column] ORDER BY [column name] 
 ) AS sequence_id,
    *
 FROM
    q
)
SELECT
*
FROM
  sequenced