question

**From the original comment:** There are 2 problems in your script: 1): the list of columns to unpivot includes the TXDATE and TRANTYPE which should never happen (this is what causes the type mismatch); 2): the select list must include all columns, not just the TXDATE and TRANTYPE because if you do that then all you get is 2 columns in your results, but you need 4 (TXDATE, TRANTYPE, **data** representing the column value and **d** representing the column name). To make the script work, please modify the part generating the column list to exclude the first 2 columns, and also select all columns coming out of your pivot, not just first 2. Here is the complete script which works as expected: declare @colsUnPivot nvarchar(max); declare @query nvarchar(max); select @colsUnPivot = stuff(( select ',' + quotename(name) from sys.columns c where [object_id] = object_id(N'dbo.AccountingData') and column_id > 2 for xml path('')), 1, 1, '' ), @query = 'select u.* from dbo.AccountingData unpivot (data for d in (' + @colsUnPivot + ')) u' exec sp_executesql @query; **Additional information and solutions to resolve the issue with different data types:** If any of the columns participating in the pivot list differ from others by data type then the script displayed above will not work as written. This is by design, i.e. all columns must be of the same type if you do decide to use the UNPIVOT to unpivot your data. What I mean is that you don't have to use it, there is also a technique to use the **cross apply values** instead. In order to quickly view whether the data types of any of the columns are of different data type, run the select below: select f.name ColumnName, f.system_type_name DataType from sys.dm_exec_describe_first_result_set(N'select * from dbo.AccountingData', null, 0) f; While it is possible to get the same information from sys.columns, I find the [ sys.dm\_exec\_describe\_first\_result\_set][1] to be very useful because it works not only for tables but for any other selects as well. By the way, this table valued function was mentioned and used by @Phil Factor in his excellent article titled [Stairway to Exploring Database Metadata Level 2: Using the Dynamic Online Catalog to Solve Problems][2] published this morning on the sqlservercentral.com website. Look at the DataType column values from the third row down to observe whether there are any differences. Here are both solutions: **Solution with UNPIVOT which requires all columns to be of the same data type:** declare @colsUnPivot nvarchar(max); declare @columnList nvarchar(max); declare @query nvarchar(max); select @colsUnPivot = stuff(( select ',' + quotename(name) from sys.columns c where [object_id] = object_id(N'dbo.AccountingData') and column_id > 2 for xml path('')), 1, 1, '' ), @columnList = stuff(( select case when column_id <=2 then ',' + quotename(name) else ',cast(' + quotename(name) + ' as decimal(10, 2)) ' + quotename(name) end from sys.columns c where [object_id] = object_id(N'dbo.AccountingData') for xml path('')), 1, 1, '' ), @query = 'select u.* from ( select ' + @columnList + ' from dbo.AccountingData ) src unpivot (data for d in (' + @colsUnPivot + ')) u'; exec sp_executesql @query; The extra variable is used to populate the column list. It takes care of casting each column participating in unpivot to the same data type, decimal(10, 2) in this case. Below is the **solution with CROSS APPLY VALUES** which does not need the columns participating in unpivot to be of the same data type, it will work regardless of whether the data types are same or not. The script looks a bit simpler, does not need separate variables for column list and list for unpivot, and performance-wise it is probably close to the the one with UNPIVOT if not better. The idea is to select the rows from the original table cross applying with the pair of column value, column name combinations from the same table: declare @columnList nvarchar(max); declare @query nvarchar(max); select @columnList = stuff(( select ',(a.' + quotename(name) + ', ''' + name + ''')' from sys.columns c where [object_id] = object_id(N'dbo.AccountingData') and column_id > 2 for xml path('')), 1, 1, '' ), @query = 'select a.TXDATE, a.TranType, cap.* from dbo.AccountingData a cross apply ( values ' + @columnList + ' ) cap(data, d) ' exec sp_executesql @query; As per @GPO suggestion, it makes sense to **select @query** instead of or before executing to see how the generated dynamic script looks like. Hope this helps. Oleg [1]: https://msdn.microsoft.com/en-us/library/ff878258.aspx [2]: http://www.sqlservercentral.com/articles/Stairway+Series/145317/