Although this an old post, I am sharing another working example.
"COLUMN COUNT AS WELL AS EACH COLUMN DATATYPE MUST MATCH WHEN 'UNION' OR 'UNION ALL' IS USED"
Let us take an example:
In SQL if we write - SELECT 'column1', 'column2' (NOTE: remember to specify names in quotes) In a result set, it will display empty columns with two headers - column1 and column2
I had seven columns with few different datatypes in SQL. I.e. uniqueidentifier, datetime, nvarchar
My task was to retrieve comma separated result set with column header. So that when I export the data to CSV I have comma separated rows with first row as header and has respective column names.
SELECT CONVERT(NVARCHAR(36), 'Event ID') + ', ' +
'Last Name' + ', ' +
'First Name' + ', ' +
'Middle Name' + ', ' +
CONVERT(NVARCHAR(36), 'Document Type') + ', ' +
'Event Type' + ', ' +
CONVERT(VARCHAR(23), 'Last Updated', 126)
UNION ALL
SELECT CONVERT(NVARCHAR(36), inspectionid) + ', ' +
individuallastname + ', ' +
individualfirstname + ', ' +
individualmiddlename + ', ' +
CONVERT(NVARCHAR(36), documenttype) + ', ' +
'I' + ', ' +
CONVERT(VARCHAR(23), modifiedon, 126)
FROM Inspection
Above, columns 'inspectionid' & 'documenttype' has uniqueidentifer
datatype and so applied CONVERT(NVARCHAR(36))
. column 'modifiedon' is datetime and so applied CONVERT(NVARCHAR(23), 'modifiedon', 126)
.
Parallel to above 2nd SELECT
query matched 1st SELECT
query as per datatype of each column.