dive into mark

“I have a result set that represents data in a flat file, with field names and lengths. How do I calculate the position of each field, i.e. the sum of all the lengths of the fields before it?”

This answer uses Microsoft SQL Server. I’ll post an Oracle version later. For simplicity, let’s assume the data is in this table:

create table data (
  id int identity primary key not null,
  name varchar(40) not null,
  length int not null
)
go

insert into data
select 'Column A', 14
union all
select 'Column B', 7
union all
select 'Column C', 9
union all
select 'Column D', 5
go

select * from data
go

id          name                                     length      
=========== ======================================== =========== 
1           Column A                                 14
2           Column B                                 7
3           Column C                                 9
4           Column D                                 5

(4 row(s) affected)

In a procedural language, we would simply loop through this result set from top to bottom, keeping a position variable and adding lengths to it as we go. We could do that in SQL with cursors, but it’s not what SQL is good at. Instead, think relationally: if we had an intermediate result set that listed each ID and all the lengths of all the IDs before it, we could sum up those lengths and group by ID. We can do this by joining the table to itself, but using a non-equi join (< instead of =).

select a.id, b.length
from data a
join data b on b.id < a.id

id          length      
=========== =========== 
2           14
3           14
3           7
4           14
4           7
4           9

(6 row(s) affected)

Oops! This eliminates ID 1 because there are no rows with ID < 1. We can fix this by using an outer join instead of an inner join.

select a.id, b.length
from data a
left outer join data b on b.id < a.id

id          length      
=========== =========== 
1           NULL
2           14
3           14
3           7
4           14
4           7
4           9

(7 row(s) affected)

Closer, but what we really want is for ID 1 to be associated with a value of 0, not NULL. So we need to use the COALESCE function, which returns the first argument if it’s not null, and the second argument otherwise.

select a.id, coalesce(b.length, 0)
from data a
left outer join data b on b.id < a.id

id                      
=========== =========== 
1           0
2           14
3           14
3           7
4           14
4           7
4           9

(7 row(s) affected)

Now sum up the lengths by ID, and that’s the position for each field.

select a.id, sum(coalesce(b.length, 0)) position
from data a
left outer join data b on b.id < a.id
group by a.id

id          position    
=========== =========== 
1           0
2           14
3           21
4           30

(4 row(s) affected)

Now we need to join these results back to the original data to get the rest of the values (like the field names). We could make this intermediate result set into a view and join it to the table, but that’s a pain in the ass and not really necessary since we’re never going to reuse this query for anything else; it’s not really its own query, it’s just a part of a larger one. So we can use a derived table, which is basically an in-line view: you define it in parentheses, give it a name (here we’re calling it “derived_table”), and then use it in the rest of the query.

select data.*
, derived_table.position
from data
inner join (
  select a.id, sum(coalesce(b.length, 0)) position
  from data a
  left outer join data b on b.id < a.id
  group by a.id
) derived_table on data.id = derived_table.id

id          name                                     length      position    
=========== ======================================== =========== =========== 
1           Column A                                 14          0
2           Column B                                 7           14
3           Column C                                 9           21
4           Column D                                 5           30

(4 row(s) affected)

Presto! SQL rocks.