Jeff Jeff - 3 months ago 21
SQL Question

Error: Ambiguous column name

Why am I getting this error?


Msg 209, Level 16, State 1, Line 94

Ambiguous column name 'New Value'.


Query:

SELECT
aho2.[Control Number] AS [Control Number],
STUFF((SELECT '; ' + [Old Value] as [text()]
FROM #AuditHistoryOutput aho1
WHERE [aho1].[Control Number] = [aho2].[Control Number]
FOR XML PATH('')), 1, 1, '') [Unset Choice Value],
STUFF((SELECT '; ' + [New Value] as [text()]
FROM #AuditHistoryOutput aho2, #AuditHistoryOutput aho1
WHERE [aho1].[Control Number] = [aho2].[Control Number]
FOR XML PATH('')), 1, 1, '') [Set Choice Value]
FROM
#AuditHistoryOutput aho2

Answer

you use the aho2 table alias twice change one of the references to something else

it looks like you are doing string conactenation of rows to a semi colon delimited string. But if you look in your second stuff statement you use the table alias aho2 and then you use it again in the last table reference. So one of the 2 references need to change otherwise sql-server doesn't know which one you are referencing.

But now that I look deeper you also have an issue in your second select statement that you have a cross join specified due to implicit join sytax and specifying the table twice. My guess is you don't want that either here is one way (a guess) that might get you want you want but if not you should update your question with schema, example data, and desired result so that we can more effectively assist you.

SELECT 
    aho3.[Control Number] AS [Control Number]
    ,STUFF(
       (SELECT '; '+[Old Value] as [text()]
       FROM #AuditHistoryOutput aho1
       WHERE [aho1].[Control Number] = aho3.[Control Number]
       FOR XML PATH(''))
       , 1, 1, '') [Unset Choice Value]
    ,STUFF(
       (SELECT '; '+[New Value] as [text()]
       FROM #AuditHistoryOutput aho2
       WHERE [aho2].[Control Number] = aho3.[Control Number]
       FOR XML PATH(''))
       , 1, 1, '') [Set Choice Value]
FROM #AuditHistoryOutput aho3