sqlsql-servert-sql

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