Dynamic SQL conflict

Transact-SQL
1

Hi, I had the following query in my sql.

USE Table_A

select C.name
from sys.columns c
where c.object_id = OBJECT_ID('TRANSACTIONS.T_Transaction') 

DECLARE @colsUnpivot AS NVARCHAR(MAX),
   @query  AS NVARCHAR(MAX),
   @table As Varchar(50) = 'TRANSACTIONS.T_Transaction'

select @colsUnpivot 
  = stuff((select ','+ convert(varchar(50), quotename(C.name))
           FROM sys.columns c
           WHERE c.object_id = OBJECT_ID(@table) 
           for xml path('')), 1, 1, '') 

set @query 
  = 'select data
     from ' + (@table) + '
     unpivot
     (
        data
        for d in ('+ @colsunpivot +')
     ) u'

exec sp_executesql @query;

However I get the following error when trying to execute:
The type of column "Company_ID" conflicts with the type of other columns specified in the UNPIVOT list.

May i know how can I amend my query?

2

The key is to convert all your columns to a data type. like varchar or int.

Can you post DDL of your table ? TRANSACTIONS.T_Transaction

A solution is to do a change like this:

DECLARE @colsUnpivot AS NVARCHAR(MAX),
   @query  AS NVARCHAR(MAX),
    @tableList As Varchar(50) =  'TRANSACTIONS.T_Transaction',
   @tableList As Varchar(5000) = '(SELECT CAST([TransactionID] AS VARCHAR(30)) AS  [TransactionID]  , CAST([Company_ID] AS VARCHAR(30)) AS [Company_ID]               ,CAST(TransactionDate AS VARCHAR(30)) AS [TransactionDate] FROM  TRANSACTIONS.T_Transaction)A'

....

set @query 
  = 'select data
     from ' + (@tableList) + '
     unpivot
     (
        data
        for d in ('+ @colsunpivot +')
     ) u'
exec sp_executesql @query;
3

From AdventureWorks2008R2, I used the follow script:

select C.name
from sys.columns c
where c.object_id = OBJECT_ID('Production.TransactionHistory') 

DECLARE @colsUnpivot AS NVARCHAR(MAX),
   @query  AS NVARCHAR(MAX),
   @table As Varchar(50) = 'Production.TransactionHistory' ,
   @tableList As Varchar(4000) = '(SELECT TOP 10 CAST([TransactionID] AS VARCHAR(30)) AS [TransactionID]
            ,CAST(TransactionDate AS VARCHAR(30)) AS [TransactionDate]
            ,CAST(ProductID AS VARCHAR(30)) AS [ProductID]
            ,CAST(ReferenceOrderID AS VARCHAR(30)) AS [ReferenceOrderID]
            ,CAST(ReferenceOrderLineID AS VARCHAR(30)) AS [ReferenceOrderLineID]
            ,CAST(TransactionType AS VARCHAR(30)) AS [TransactionType]
            ,CAST(Quantity AS VARCHAR(30)) AS [Quantity]
            ,CAST(ActualCost AS VARCHAR(30)) AS [ActualCost]
            ,CAST(ModifiedDate AS VARCHAR(30)) AS [ModifiedDate]
            FROM  [Production].[TransactionHistory]) A'



select @colsUnpivot 
  = stuff((select ','+ convert(varchar(50), quotename(C.name))
           FROM sys.columns c
           WHERE c.object_id = OBJECT_ID(@table) 
           for xml path('')), 1, 1, '') 

print @colsUnpivot



set @query 
  = 'select data
     from ' + (@tableList) + '
     unpivot
     (
        data
        for d in ('+ @colsunpivot +')
     ) u'
print @query

exec sp_executesql @query;