2uzhan.com
Advertisement
Now Place:2uzhan.com » Inserting blank row with a certain criteria.

Inserting blank row with a certain criteria.

MySQL @ October 22, 2008   Views:0

I have a table with a set number of policy numbers and details attached to it. I would like to create a blank row between each set of policy numbers. For example,

POL NUMBER ENDSTATUS ENDDATE
0001 first 10/11
0001 second 10/14
0001 third 10/16
0001 fourth 10/18
<blank row>
0002 first 10/16
0002 second 10/22

Is there a way to do this?

Thanks much!

--------------Solutions-------------

I think you're confusing data storage with data display.

This kind of thing is easy to do with a loop in (for example) php.

That said, you could also do something like this:

So say you have a table like this:

SELECT * FROM orders;
+----------+---------+------+
| order_id | item_id | qty |
+----------+---------+------+
| 1 | 1 | 12 |
| 1 | 2 | 11 |
| 1 | 3 | 10 |
| 2 | 2 | 9 |
| 2 | 3 | 8 |
| 2 | 4 | 7 |
| 3 | 3 | 6 |
| 3 | 4 | 5 |
| 3 | 5 | 4 |
+----------+---------+------+

Then this could give you a result similar to what you're after:

SELECT DISTINCT(order_id)order_id,NULL item_id,NULL qty FROM orders
UNION ALL
SELECT order_id,item_id,qty FROM orders
ORDER BY order_id,item_id;
+----------+---------+------+
| order_id | item_id | qty |
+----------+---------+------+
| 1 | NULL | NULL |
| 1 | 1 | 12 |
| 1 | 2 | 11 |
| 1 | 3 | 10 |
| 2 | NULL | NULL |
| 2 | 2 | 9 |
| 2 | 3 | 8 |
| 2 | 4 | 7 |
| 3 | NULL | NULL |
| 3 | 3 | 6 |
| 3 | 4 | 5 |
| 3 | 5 | 4 |
+----------+---------+------+

Edited 1 time(s). Last edit at 10/23/2008 11:13AM by laptop alias.

Tags:
© 2018 2uzhan.com Contact