Tests.Directory_id = '+@id+')

Tests.Directory_id = '''+@id+''')

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
declare @id nvarchar(max) = 'BE4DB2CE-C95D-E411-9FEA-00215AA6C898'

SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(Question_id)
FROM (SELECT DISTINCT UserQuestions.Question_id FROM UserQuestions where UserQuestions.UserTest_id
			 in (select UserTest_id from UserTests where UserTests.Directory_Test_id = @id)) AS Courses;

--select @ColumnName
set @DynamicPivotQuery = N'select  FIO, '+@ColumnName+' from 
(
select
      --Directory_name as DirectoryName
	  Users.User_surname + users.User_name + Users.User_patronymic as FIO
	  --,Org_name as ORG
	  ,UserTests.UserTest_id as UserTestID
	  --,UserQuestions.Question_id as QuestionId
	  --,REPLACE(UserQuestions.Question_content, char(10), '') as [Вопрос]
	  ,cast(UserQuestions.Question_isTrue as decimal (5,2)) as result
	  --,cast(sum(UserQuestions.Question_isTrue) over (partition by UserTests.UserTest_id) as decimal (5,2)) as TotalResult
  FROM [attestation].[dbo].[Directories]
  inner join Tests on Directories.Directory_id = Tests.Directory_id
  inner join UserTests on UserTests.Directory_Test_id = Tests.Directory_id
  inner join Users on Users.User_id = UserTests.User_id
  inner join Orgs on Orgs.Org_id = Users.Org_id
  inner join UserQuestions on UserQuestions.UserTest_id = UserTests.UserTest_id
  where Tests.Directory_id = '''+@id+''') as x
  pivot (
  max(result) for UserTestID in ('+@ColumnName+')
  ) as pvt'

  execute (@DynamicPivotQuery)

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
declare @id nvarchar(max) = 'BE4DB2CE-C95D-E411-9FEA-00215AA6C898'

SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(Question_id)
FROM (SELECT DISTINCT UserQuestions.Question_id FROM UserQuestions where UserQuestions.UserTest_id
			 in (select UserTest_id from UserTests where UserTests.Directory_Test_id = @id)) AS Courses;

--select @ColumnName
set @DynamicPivotQuery = N'select  DirectoryName, FIO, ORG,CountQuestion,TotalResult,TotalPercent,'+@ColumnName+'  from 
(
select
      Directory_name as DirectoryName
	  ,Users.User_surname +'' ''+ users.User_name +'' ''+ Users.User_patronymic as FIO
	  ,Org_name as ORG
	  ,UserTests.UserTest_id as UserTestID
	  ,UserQuestions.Question_id as QuestionId
	  --,REPLACE(UserQuestions.Question_content, char(10), '') as [Вопрос]
	  ,cast(UserQuestions.Question_isTrue as decimal (5,2)) as result
	  ,cast(sum(UserQuestions.Question_isTrue) over (partition by UserTests.UserTest_id) as decimal (5,2)) as TotalResult
	  ,cast(count(UserQuestions.Question_isTrue)over (partition by UserTests.UserTest_id) as decimal (5,2)) as CountQuestion
	  ,cast((sum(UserQuestions.Question_isTrue) over (partition by UserTests.UserTest_id)) * 100 / (count(UserQuestions.Question_isTrue)over (partition by UserTests.UserTest_id)) as decimal(5,2)) as TotalPercent
  FROM [attestation].[dbo].[Directories]
  inner join Tests on Directories.Directory_id = Tests.Directory_id
  inner join UserTests on UserTests.Directory_Test_id = Tests.Directory_id
  inner join Users on Users.User_id = UserTests.User_id
  inner join Orgs on Orgs.Org_id = Users.Org_id
  inner join UserQuestions on UserQuestions.UserTest_id = UserTests.UserTest_id
  where Tests.Directory_id = '''+@id+''') as x
  pivot (
  max(result) for QuestionId in ('+@ColumnName+')
  ) as pvt'

  execute (@DynamicPivotQuery)
  print(@DynamicPivotQuery)