title: SQL Pivot query puts same value in every column
body: <p>I need some help in creating the query using pivot which I have created but I am facing issue that when I apply aggregate funtion on any column query puts that value for every column. </p>
  
  <pre><code>create table #skillsTemp
  (
      RowNumber INT IDENTITY(1, 1),
      ResourceId INT,
      Caption VARCHAR(MAX),
      SubPracticeName VARCHAR(1000),
      SkillName VARCHAR(MAX),
      IsActive BIT,
      IsCoreSkill INT,
      TrainingTypeId INT
  )
  
  
  INSERT INTO #skillsTemp (ResourceId, Caption, SubPracticeName, SkillName, IsActive, IsCoreSkill, TrainingTypeId)
  
  SELECT Resource.ResourceId, Resource.Caption, SubPracticeName, SkillName, IsActive, MAX(IsCoreSkill) IsCoreSkill,  ResourceAttribute.TrainingTypeId FROM Resource INNER JOIN
  SubPractice ON SubPractice.SubPracticeId = Resource.SubPracticeId LEFT OUTER JOIN
  SkillMaster ON SkillMaster.SubPracticeId = SubPractice.SubPracticeId LEFT OUTER JOIN
  ResourceAttribute ON ResourceAttribute.ResourceId = Resource.ResourceId 
  
  WHERE ResourceTypeId = 6 AND Resource.IsDeleted = 0 AND SkillMaster.SubPracticeId = 1
  GROUP BY Resource.ResourceId, Resource.Caption, SkillName, SubPracticeName, ResourceAttribute.TrainingTypeId, IsActive
  
  
  DECLARE @cols AS NVARCHAR(MAX),
      @query  AS NVARCHAR(MAX),
      @secondaryCols AS NVARCHAR(MAX)
  
  SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.SkillName)
              FROM #skillsTemp c
              where IsCoreSkill = 1
              FOR XML PATH(''), TYPE
              ).value('.', 'NVARCHAR(MAX)') 
          ,1,1,'')
  SET @secondaryCols = STUFF((SELECT distinct ',' + QUOTENAME(c.SkillName)
      FROM #skillsTemp c
      WHERE IsCoreSkill = 0
      FOR XML PATH(''), TYPE
      ).value('.', 'NVARCHAR(MAX)') 
  ,1,1,'')
  
  set @query = 'SELECT  Caption, ''A'' AS Priority, ' + @cols + ', ' + @secondaryCols + '  from 
              (
                  select ResourceId
                      , Caption
                      , SkillName,
                      IsActive
                  from #skillsTemp
             ) x
              pivot 
              (
                  MAX(ResourceId) 
                  for SkillName in (' + @cols + ', ' + @secondaryCols + ')
              ) p'
  </code></pre>
  
  <p>SELECT @query    </p>
  
  <p>EXECUTE( @query)</p>
  
  <p>This is my whole query. It returns 2 records but it fill ReourceId at every column as value. What I need is If Fico have some then It should show to Fico and to particular instead of showing to all columns. </p>
  
  <blockquote>
    <p>Caption, Priority, FICO, Cash Mgt are columns, while X and Y are different values. </p>
  </blockquote>
  
  <p>Actual output:</p>
  
  <p>Caption               Priority  FICO    Cash Mgt. 
  Shreesh Testing         A       28983   28983<br>
  Test   Test             A       28966   28966   </p>
  
  <p>Expected Output:
  Caption             Priority    FICO    Cash Mgt.
  Shreesh Testing       A          X         0<br>
  Test Test             A          0         Y</p>