TSQL Grouping and Ordering for Dynamic ASP.Net Table

Question

I hoping someone may be able to point me in the right direction for this...

Basically Im writing an asp.net web control to dynamically display a set of data based on field data stored in the database.

I want the control to create a dynamic asp table from a set of data from the database, however I cant get my head round the query needed to order the data into a list that I can create a htmltable from (if that makes sense)

my data looks like this, where I have a field name, the order the corresponding control will be rended in, and the number of columns that control takes up in the asp table:

Id | FieldName | ColumnSpan | Order ...
-----------------------------------------
1  | UserName   | 1   |   1
2  | FirstName   | 1   |   3
3  | LastName   | 1   |   4
4  | Email Address   |   2   |   2
5  | DOB           | 1   |   5
6  | Notes         | 2   |   7
7  | Password   | 1   |   6
8  | UserID       | 1   |   0

I have made a couple of attempts at creating a stored procedure that accepts an integer representing the number of columns the table will have, with no real progress. I would like the query to return something like the following (if the input parameter was 2 (columns)) - where the fields are grouped into logical rows

row | FieldName | Order
------------------------------------
1      | UserId        | 0
1      | UserName  | 1
2      | Email Address        | 2
3      | FirstName    | 3
3      | LastName    | 4
4      | DOB           | 5
4      | Password   | 6
5      | Notes         | 7

If these ramblings make any sense to anyone, I would appreciate any guidance....

Cheers kmoo01

Answer 1

This works for 2 but not for 3 (because it's not smart enough to handle the remainder properly)

-- SO2992861
DECLARE @width AS int = 2
DECLARE @t AS TABLE (Id int, FieldName varchar(15), ColumnSpan int, [Order] int)
INSERT INTO @t
VALUES (1, 'UserName', 1, 1)
 ,(2, 'FirstName', 1, 3)
 ,(3, 'LastName', 1, 4)
 ,(4, 'Email Address', 2, 2)
 ,(5, 'DOB', 1, 5)
 ,(6, 'Notes', 2, 7)
 ,(7, 'Password', 1, 6)
 ,(8, 'UserID', 1, 0)

SELECT Row, FieldName, [Order]
FROM(
    SELECT l.FieldName
        ,l.ColumnSpan
        ,l.[Order]
        ,SUM(r.ColumnSpan) AS RunningTotal
        ,SUM(r.ColumnSpan) / @width + CASE WHEN SUM(r.ColumnSpan) % @width <> 0 THEN 1 ELSE 0 END AS Row
    FROM @t AS l
    LEFT JOIN @t AS r
        ON r.[Order] <= l.[Order]
    GROUP BY l.FieldName, l.ColumnSpan, l.[Order]
) AS X
ORDER BY [Order]