tsql format as percentage

I have the following sql statement:

SELECT 1, 2, 3

UNION ALL

SELECT CAST(1 AS VARCHAR) + '%',CAST(2 AS VARCHAR) + '%',CAST(3 AS VARCHAR) + '%'

And I am getting the following error:

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

I could change the first select statement to '1','2','3', but i have a many unions and only one row that has to have the percentage format. Is there 开发者_如何学运维any other alternatives that are available?

Thanks!

You need to CAST all union clauses.

Every row in the final output with have the highest precedence datatype. int is higher than varchar, so 1% is cast to int with obvious results

select cast(1 as varchar), cast(2 as varchar), cast(3 as varchar)
union all
select cast(1 as varchar) + '%',cast(2 as varchar) + '%',cast(3 as varchar) + '%'

Or: you can not have a different datatypes/formats in one column.

Of course this is an example, but mixing ints and formatted floats makes no sense even if SQL did allow it

try this

select cast(1 as varchar) ,cast(2 as varchar), cast(3 as varchar)

union all

select cast(1 as varchar) + '%',cast(2 as varchar) + '%',cast(3 as varchar) + '%'

Reason of error is you above select having 1,2,3 which is an integer values and you are doing union with the varchar value

so solution is you need to convert you above select data type to varchar

The second solution is

Select cast(col1 as varchar),cast(col2 as varchar),........... from
(
   .. all your union large query you have writting
) d

union all
select cast(1 as varchar) + '%',cast(2 as varchar) + '%',cast(3 as varchar) + '%'