1

Did I stumble across a reserved word here or something?

For, the following statement:

SELECT [Type] 
FROM (SELECT 'Receive' AS [Rec], 'Transfer' AS [Trx]) [data]
UNPIVOT
(
    [Orders] FOR [Type] IN ([Rec], [Trx])
) AS [unpvt];

produces this exception/error: The type of column "Trx" conflicts with the type of other columns specified in the UNPIVOT list.

Working Sample

SELECT [Type]    
FROM 
--(SELECT 'Receive' AS [Rec], 'Transfer' AS [Trx]) [data]
(SELECT 'Rec_eive' AS [Rec], 'Transfer' AS [Trx]) [data]
UNPIVOT
(
    [Orders] FOR [Type] IN ([Rec], [Trx])
) AS [unpvt];

Database Explorer: data.stackexchange.com | Simple Unpivot - Column Conflict

Hannah Vernon
  • 70,041
  • 22
  • 171
  • 315
Brett Caswell
  • 111
  • 4
  • umm... in what sense could you people confuse this as a duplicate question? because the ambigious answer sentiment of the accepted answer?.. this isn't a datatype/length issue at all... CONVERT(varchar(16), 'Receive') [Rec] doesn't resolve the issue... – Brett Caswell Jul 24 '15 at 21:15
  • 5
    It has to be same length .. Did you even read the question ? SELECT [Type] FROM (SELECT cast('Receive' as varchar(8)) AS [Rec], 'Transfer' AS [Trx]) [data] UNPIVOT ( [Orders] FOR [Type] IN ([Rec], [Trx]) ) AS [unpvt]; works ! I marked this a dupe because your resolution is exactly the same - datatype length should be same - not more or less. – Kin Shah Jul 24 '15 at 21:27
  • yeah.. I tried it.. I provided a nice little Database Explorer for that exact purpose.. the better question is, did any of yall try it before you asserted that this question and that other question, as well as the answers are the same?.. – Brett Caswell Jul 24 '15 at 21:35
  • 3
    Brett, since the issue is resolved by using SELECT [Type] FROM (SELECT CONVERT(VARCHAR(32),'Receive') AS [Rec], CONVERT(VARCHAR(32),'Transfer') AS [Trx]) [data] UNPIVOT ( [Orders] FOR [Type] IN ([Rec], [Trx]) ) AS [unpvt];, why are you so insistent that this isn't a datatype/length issue at all? I can assure you it's not a reserved word problem, and I can also assure you you're not going to get a more detailed answer here than what Paul already provided on the original question. This absolutely fits the textbook definition of a duplicate, sorry. – Aaron Bertrand Jul 24 '15 at 21:42
  • (Also, please refrain from adding meta commentary about flagging, moderation etc. to the question. If you have an issue with flagging, take it to meta, that's what it's for.) – Aaron Bertrand Jul 24 '15 at 21:45
  • @ypercube the "issue" is he had to add a space to the first column in order for the data lengths to match, and believes this is caused by something other than a data length issue... – Aaron Bertrand Jul 24 '15 at 21:48
  • @AaronBertrand's solution works.. (confirms the dup source question in fact share the same underlining issue).. still, this answer/comment/solution actually solves a problem/instance.. and the other is a generalized question.. with an ambigious comment containing a good hypothesis as the answer.... and actually, Kin was correct.. I mustn't had done 8, as I thought I had.. – Brett Caswell Jul 24 '15 at 21:59
  • and actually.. I think this is the better solution: http://sqlfiddle.com/#!3/13b9a/2/0 – Brett Caswell Jul 24 '15 at 22:04
  • 1
    No, don't use SQL_VARIANT - that is horrible for many reasons I'm not going to get into from a mobile device, but please, just don't – Aaron Bertrand Jul 24 '15 at 23:29

0 Answers0