CODEWITHSUNDEEP
sqlsql-servertype-conversionunion
DSTL
DSTL

Reputation: 45

Union queries causing type conversion error

I'm trying to work out why my union queries are giving me type conversion errors

I am trying to create a report that has to be formatted in a way that multiple union queries seems like the easiest option. Essentially I want an output that shows the key column, and then a row for each other column, and the value for that column, so it would kind of look like this:

Key1  Column1   Value
Key1  Column2   Value
Key2  Column1   Value
Key2  Column2   Value

I'm doing this for about 40 columns though, so its a lot of select statements.

All of my select statements are structured the same:

SELECT tb1.Key AS 'Key', 'Column1' AS 'Column', tb1.Column1 AS 'Value'
union
SELECT tb1.Key AS 'Key', 'Column2' AS 'Column', tb1.Column2 AS 'Value'

etc

When running all of the queries individually I have no issue, but when I try to run all, of them I get an issue converting various strings to type int.

In order to try rectifying this I've tried casting every 'value' column as NVARCHAR and I still get an error that some of the strings are trying to be converted to type int, but I'm not sure why that is.

Upvotes: 0

Views: 2958

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

The problem is that the columns for a union need to be of the same type. Typically, you can store values as strings.

More importantly, you don't need union for this. SQL Server supports lateral joins using the apply keyword. This is simpler to express as:

select tb1.key, v.column, v.value
from tb1 cross apply
     (values ('Column1', convert(nvarchar(max), tb1.Column1),
             ('Column2', convert(nvarchar(max), tb1.Column2),
             . . . 
     ) v(column, value);

Upvotes: 1

ravioli
ravioli

Reputation: 3823

Generally when doing a UNION, the data types of the columns of your result set will be determined by the first query that executes or returns rows. So, if you're not sure which queries in the UNIONs return data you should CAST all your columns explicitly. Something like:

SELECT 
  CAST(tb1.Key AS INTEGER) AS 'Key', 
  CAST('Column1' AS VARCHAR(100)) AS 'Column', 
  CAST(tb1.Column1 AS VARCHAR(100)) AS 'Value'
FROM MyTableA AS tb1

UNION

SELECT 
  CAST(tb1.Key AS INTEGER) AS 'Key', 
  CAST('Column1' AS VARCHAR(100)) AS 'Column', 
  CAST(tb1.Column1 AS VARCHAR(100)) AS 'Value'
FROM MyTableB AS tb1

If your UNIONs are simple SELECTs from the same table, it shouldn't be a problem. But the issue may come up when some rows return NULL values and data types may not be what you expect.

Upvotes: 2

Related Questions