Skip rows in query based on data

Question

I have data as follow

DECLARE @tmp TABLE(cid int, colspan int, rowspan int, corder int)
INSERT INTO @tmp
SELECT 1,2,null,1
UNION
SELECT 2,null,null,2
UNION
SELECT 3,null,null,3
UNION
SELECT 4,3,null,4
UNION
SELECT 5,null,null,5
UNION
SELECT 6,null,null,6
UNION
SELECT 7,null,null,7

I want to query to return

cid   colspan  rowspan  corder
-------------------------------------
1      2       null      1
3      null    null      3
4      3       null      4
7      null    null      7

Records will be ordered by corder and then using colspan value skip next records (if colspan = 2 skip next 1, if 3 skip next 2). Is this possible to do using query?

Why I want this data - I'm trying to bind this data to repeater control (table template) to create a dynamic table and when there is colspan > 0 i dont want my repeater to generate td items so that it will span correctly.

Answer 1

You can use CLR Table Valued functions that returns the actual table. CLR functions are functions you can write in your native language C#, VB etc. and the dll are embedded in the database.

Learn more about it from here:

http://msdn.microsoft.com/en-us/library/ms131103.aspx

Answer 2

Because (As @Sam Saffron said in comments) it is indeed much much easier to do this kind of stuff in procedural code, it means that your table is not designed properly for SQL.

In a SQL database, each row should describe a single entity (or a single relationship between entities). Failing to do so if a common enough design flaw. For example, consider this Payroll table:

CREATE TABLE Payroll
(
 employee_number CHAR(10) NOT NULL
    REFERENCES Personnel (employee_number), 
 effective_date DATE NOT NULL, 
 salary_amount DECIMAL(19, 4) NOT NULL
    CHECK (salary_amount >= 0), 
 UNIQUE (effective_date, employee_number)
);

INSERT INTO Payroll (employee_number, effective_date, salary_amount)
   VALUES ('U83GHVPSGP', '2001-01-01', 5000), 
          ('U83GHVPSGP', '2002-01-01', 7000), 
          ('U83GHVPSGP', '2002-01-01', 9000);

The problem here is that it is modelling periods but each period's end date is dervied from another row's start date i.e. the entity (being a single period of salary) is modelled using two rows and each start date is playing two roles in the database. A side effect is that a simple query such as, "Get me the period employee U83GHVPSGP was paid 5000 MNT" is non-trivial (from an implementation point of view, it will involve a correlated subquery which is likely to perform badly on a given SQL platform). The above table will suffer non-obvious anomalies e.g. deleting the row for which U83GHVPSGP received 7000 MNT will implicitly change the data on another rows i.e. it now seems like U83GHVPSGP was paid 5000 MNT until '2002-01-01' when this wasn't actually so.

The fact that you have a column named rowspan is the most perfect example I've ever seen of a 'smell' for this kind of design flaw.

I don't mean to sound harsh. Your table no doubt makes perfect sense for procedural code, so use procedural code and not SQL.

SQL works best with set based solutions, so if you want to use SQL then consider redesigning your table to model entities in separate tables from the relationship between entities and ensure that both flavours of table do not split a single entity's/relationship's data across multiple rows.

Answer 3

Try using a recursive CTE, like so:

with cte as (
select t.* from @tmp t where corder = 1
union all 
select t.* from @tmp t join cte c on t.corder = (c.corder + coalesce(c.colspan,1))
)
select * from cte