The column prefix 'a' does not match with a table name

Ayesha Fathima

8y853SQL

Result

column-2068 2069 2055

value- usr1 usr2 usr3

I need to join one more table to the existing table on roleid ,because in the result instead of numbers(like 2068 etc) i need related rolenames

Iam trying to do like this

it is saying error like

SQL Error (107): The column prefix 'a' does not match with a table name or alias name used in the query. */

DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(a.role_id)
from form_appr_workflow a join win_role_master b on a.role_id=b.role_id where a.ref_no='LYC002-001-CHAR-2018-96'
group by a.user_id, a.role_id
order by a.role_id
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = N'SELECT ' + @cols + N' from
(
select a.user_id,a.role_id
from form_appr_workflow a
) x
pivot
(
max(a.user_id)
for a.role_id in (' + @cols + N')
) p '
exec sp_executesql @query;

Expected result

column-coo chief head

value- usr1 usr2 usr3

2 Replies

Know the answer? Post it — somebody with the same question will find it here.