Creating Two Different Data Types for Table

I desire to make a header of table in the columns containing integer data type for giving number each data sequentially and string data type for the header. The table illustrated below

No	ReferenceNumber	Amount
Transactions Reporting
for the Period of Q4 in 2023

1	159	-510
2	159	-512
3	160	-523

Previously, the table was given like this one

No	ReferenceNumber	Amount
1	159	-510
2	160	-523
3	159	-512

However, with these queries below

CREATE TABLE Table1(
No int identity(1,1),
ReferenceNumber varchar(255),
Amount varchar(255))

Insert Into Table1
select cast(RefNo as varchar(255)), cast(Amount as varchar(255))
from anothertable

SELECT * 
into #FirstTempTable
FROM Table 1

SELECT 
CAST('Transactions Reporting' as int) as No,
'' as ReferenceNumber,
'' as Amount
into #SecondTempTable

SELECT 
cast('for the Period of Q4 in ' as int) + cast(year(getdate()) as int) as No,
'' as ReferenceNumber,
'' as Amount
into #ThirdTempTable

SELECT 
cast('' as int) as No,
'' as ReferenceNumber,
'' as Amount
into #FourthTempTable

SELECT * FROM #SecondTempTable
union all
SELECT * FROM #ThirdTempTable
union all
SELECT * FROM #FourthTempTable
union all 
SELECT * FROM #FirstTempTable

I acquired the error result below

Conversion failed when converting the varchar value 'NO' to data type int.

I know there is an unmatched data type, but with int identity it's matched for solving the unordered data. Please support for obtaining desired data like the first table display.

Big thanks for your helps

Solution

You can do something like this:

select *
into #data
from (
    VALUES  (1, 159, -510)
    ,   (2, 160, -523)
    ,   (3, 159, -512)
) t (No,ReferenceNumber,Amount)


select header, referencenumber, amount
from (
    select -10 as sort, N'Transactions Reporting' as header, null as referencenumber, null as amount
    union all
    select -9, concat(N'for the Period of Q4 in ', year(getdate())), null, null
    union all
    select no, cast(no as nvarchar(100)), referencenumber, amount
    from #data
    ) x
order by sort

The trick is to create a sort field. I usually use negative numbers for simplicity, but whatever goes.

The second thing is that all corresponding columns in the union should have same datatype, otherwise the server will try to cast around things. This is why i cast(no as nvarchar(100))

Output:

header	referencenumber	amount
Transactions Reporting	NULL	NULL
for the Period of Q4 in 2023	NULL	NULL
1	159	-510
2	160	-523
3	159	-512