Adding dummy lines

  • Thread starter Thread starter Tor Inge Rislaa
  • Start date Start date
T

Tor Inge Rislaa

Adding dummy lines

Having a special need of adding dummy lines in a select statement.

My select is something like:

Select Field1, Field2, Field3, Field4 From MyTable

The result looks something like

Field1
Field2
Field3
Field4

1000
Variable Text 1
1234
150,20

1050
Variable Text 2
5432
127,10

2120
Variable Text 3
5521
10,42


I need to insert a line in the result between each line, based on some of
the data from the line above. The result should look something like:

Field1
Field2
Field3
Field4

1000
Variable Text 1
1234
150,20

1234
Static Text
1000
-150,20

1050
Variable Text 2
5432
127,10

5432
Static Text
1050
-127,10

2120
Variable Text 3
5521
10,42

5521
Static Text
2120
-10,42


Field1 in the inserted line equals Field3 in the line above and

Field3 in the inserted line equals Field1 in the line above and

The value of Field4 in the inserted line equals the negative value of Field4
in the line above.

Is this possible with an intelligent SQL statement?

TIRislaa
 
Tor Inge Rislaa said:
Adding dummy lines

Having a special need of adding dummy lines in a select statement.

My select is something like:

Select Field1, Field2, Field3, Field4 From MyTable

The result looks something like

Field1
Field2
Field3
Field4

1000
Variable Text 1
1234
150,20

1050
Variable Text 2
5432
127,10

2120
Variable Text 3
5521
10,42


I need to insert a line in the result between each line, based on some of
the data from the line above. The result should look something like:

Field1
Field2
Field3
Field4

1000
Variable Text 1
1234
150,20

1234
Static Text
1000
-150,20

1050
Variable Text 2
5432
127,10

5432
Static Text
1050
-127,10

2120
Variable Text 3
5521
10,42

5521
Static Text
2120
-10,42


Field1 in the inserted line equals Field3 in the line above and

Field3 in the inserted line equals Field1 in the line above and

The value of Field4 in the inserted line equals the negative value of Field4
in the line above.

Is this possible with an intelligent SQL statement?
Click to expand...
Hi TIRislaa,

With all due respect,
"insert a line in the result between each line" is "Excel-speak."

A query returns a set of data which can ordered
based on fields in the query.

You could return all the records you wanted with
a "UNION" query, but I do not see how you would
order them so they are "inserted between each line."

Select Field1, Field2, Field3, Field4 From MyTable
UNION ALL
Select Field3, "Static Text", Field1, "-" & Field4;

Sorry I cannot be of more help.

Gary Walter
 
That should have been:

Select Field1, Field2, Field3, Field4 From MyTable
UNION ALL
Select Field3, "Static Text", Field1, "-" & Field4 From MyTable;
 
Back
Top