SQL中的动态查询以及如何在视图中实现

param trivedi 发表于 Dev

特里维迪

在此处输入图像描述“我的查询”在一行中动态创建技能列表。但是我不能在视图中使用此查询，因为查询中存在声明变量。如何修改它以在视图中使用它并且仍然是动态的

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(col + '_' + cast(seq as             
   varchar(2)))
    from
    (
        select  seq = row_number() over(partition by Skill.ID_Job
                                    order by Skill.ID_Job) 
      from dbo.Job_Skill Skill
      Where Skill.Min_Job = 1 and Skill.Ideal_Job = 1

   ) hr

    cross apply
    (
      select 'Skill',1 
    ) c (col, val) 
                order by val , seq
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'') 



 set @query = N'SELECT ID_Job, ' + @cols + N' 
        from 
        (
          select hr.ID_Job, 
          col = c.col + ''_'' + cast(seq as varchar(2)),
          c.val
          from
          (
              select Skill.ID_Job, 
        Skill.ID_SKL_Job,
        Skill.Min_Job,
        Skill.Ideal_Job,
        seq = row_number() over(partition by Skill.ID_Job
                                    order by Skill.ID_Job) 
      from dbo.Job_Skill Skill
      Where Skill.Min_Job = 1 and Skill.Ideal_Job = 1
          ) hr

          cross apply
          (
             select ''Skill'', ID_SKL_Job
          ) c (col, val)
        ) x
        pivot 
       (
            max(val)
            for col in (' + @cols + N')
        ) p 
        order by ID_Job'

exec sp_executesql @query

在此处输入图片说明

比斯塔比尔

不确定您要做什么，但是SQL不支持动态视图。

也许在您的过程中使用FOR XML并通过输出参数返回结果XML是可行的。

然后，您可以加入XML并使用XQuery对其进行查询。如果返回的XML类似于：

DECLARE @xml xml = CONVERT(xml, 
'<jobs>
 <job id="1">
    <skill>s1</skill>
    <skill>s2</skill>
 </job>
 <job id="2">
    <skill>s3</skill>
</job>
</jobs>')

You could query for certain skills like:

SELECT 
    j.*
 ,  T.jobs.query('skill') AS skill
FROM @xml.nodes('jobs/job') T(jobs)
INNER JOIN jobs j ON j.id = t.jobs.value('@id','int')
WHERE T.jobs.exist('skill[.="s1"]') = 1