Selecting an additional empty row that does not exist [duplicate]

Question

Possible Duplicate:
Include a blank row in query results

I would like to add an empty row to my result set at the very top of my SQL query.

The query I am using is this:

SELECT 
    PROFILETITLE, DOCID 
FROM
    PROFILES
WHERE 
    COMPANYCODE = ? 
ORDER BY 
    PROFILETITLE

Answer 1

Try to use union all

SELECT null as PROFILETITLE, null as DOCID 
UNION ALL
SELECT PROFILETITLE, DOCID 
FROM PROFILES
WHERE COMPANYCODE=? 
ORDER BY PROFILETITLE

but if you wont to add header, and if DOCID is int type, you have to use union all and cast as below

SELECT 'PROFILETITLE' as PROFILETITLE, 'DOCID' as DOCID 
UNION ALL
SELECT PROFILETITLE, CAST ( DOCID AS varchar(30) )
FROM PROFILES
WHERE COMPANYCODE=? 
ORDER BY PROFILETITLE

Answer 2

Try this:

SELECT '' AS PROFILETITLE, 0 AS DOCID
UNION ALL
SELECT PROFILETITLE, DOCID
FROM PROFILES WHERE COMPANYCODE = ? 
ORDER BY PROFILETITLE

Note that when using UNION, and all other set operations, columns have to be matched in types, So you have to alias the first two empty values with PROFILETITLE and DOCID. And you have to watch out the types of the two columns as well to be matched in order to work properly. May be you need to select NULL instead of '' and 0.

Answer 3

You could add an "empty row" if all columns are varchar and empty means '' in this way:

SELECT PROFILETITLE, DOCID 
FROM PROFILES
WHERE ...

UNION ALL

SELECT '' AS PROFILETITLE, '' AS DOCID

ORDER BY 
  CASE WHEN PROFILETITLE='' AND DOCID='' THEN 0 ELSE 1 END ASC
, PROFILETITLE ASC