Merging fields in t-sql

How can I merge 2 record sets using t-sql?

let's say I have a database like the following (all float fields)

Limit - Lower - Upper - Deviation

For backwards compatability I need to return a record set like this:

Select Limit AS Val1,
Lower AS Val2,
Upper AS Val3

Which works fine, however in this one particular case I need to return the record set in two parts:

Select Limit AS Val1,
    Lower AS Val2,
    Upper AS Val3

Select Deviation AS Val1

This needs to be returned as two different row开发者_JS百科s in the same recordset. How can I do this?

---

If you are returning two separate rows in the same dataset from one table, you need to ensure each row has the same number of colums. Otherwise you should be returning two different recordsets (maybe using MARS). To return the same row twice, do this:

SELECT Limit AS Val1,    Lower AS Val2,    Upper AS Val3
FROM TableName
UNION ALL
SELECT SELECT Deviation AS Val1,    Lower AS Val2,    Upper AS Val3
FROM TableName

This will return every row in the table twice, once with Limit, Lower, Upper, and once with Deviation, Lower, Upper.

---

You cannot return a single recordset which has rows with different column definitions. Every row must have the same number of columns. Think spreadsheet, not XML.