列 "Msgtime" 的类型与 UNPIVOT 列表中指定的其他列的类型冲突

http://www.dotblogs.com.tw/ricochen/archive/2011/07/08/31356.aspx

前天在使用UNPIVOT所遇到的一個小問題，

自己是利用 CAST 轉型來解決，不知道還有沒有更好的方法，

有的話在留言告知小弟一下。 :)

Table Layout

 

 

錯誤的Statement

 

SELECT FielId,FieldName, FieldValue

FROM

(

SELECT ROW_NUMBER() OVER(ORDER BY list_id asc) AS 'FieldId',*

FROM TB_MAIL_LIST

) MyTable

UNPIVOT

(

FieldValue FOR FieldName IN (list_id,owner_group )

)AS MyUnPivot

 

 

修正的Statement

SELECT FielId,FieldName, FieldValue

FROM

(

SELECT ROW_NUMBER() OVER(ORDER BY list_id asc) AS 'FielId',

CAST(isnull(list_id,'') as nvarchar) as list_id,

CAST(isnull(owner_group,'') as nvarchar) as owner_group,

CAST(isnull([owner],'') as nvarchar) as [owner],

CAST(isnull(contact_mail,'') as nvarchar) as contact_mail,

CAST(isnull(boss_mail,'') as nvarchar) as boss_mail,

CAST(isnull(vp_mail,'') as nvarchar) as vp_mail,

CAST(isnull(create_date,'') as nvarchar) as create_date,

CAST(isnull(modify_date,'') as nvarchar) as modify_date,

CAST(isnull(modify_agentId,'') as nvarchar) as modify_agentId,

CAST(isnull(modify_agentName,'') as nvarchar) as modify_agentName,

CAST(isnull(modify_employeeId,'') as nvarchar) as modify_employeeId

FROM TB_MAIL_LIST

) MyTable

UNPIVOT

(

FieldValue FOR FieldName IN

(

list_id,

owner_group,

[owner],

contact_mail,

boss_mail,

vp_mail,

create_date,

modify_date,

modify_agentId,

modify_agentName,

modify_employeeId

)

)AS MyUnPivot

 

結果: