Reputation: 882
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
Upvotes: 1
Views: 2291
Reputation: 70528
"Ambiguous column name" means you have two tables with the same name and you don't specify which table you want. So [aho1].[New Value]
or [aho2].[New Value]
in your select list instead of [New Value]
will get rid of this error. Can't promise your query will work.
Upvotes: 0
Reputation: 14381
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
Upvotes: 2