I need some assistance pivoting my data in SQL Server. I have tried several online PIVOTs but they either are for static values or aggregate the data somehow which I don't want. Ideally I want to return the data based on the project id and flip it so I can present in a different way.

My current table stores data like this:

My desired output when selecting Project Id = 1 would be:

My desired output when selecting Project Id = 2 would be:

Any assistance/links would be much appreciated.

CodePudding user response：

This is a pretty custom requirement, and I'm not surprised you haven't found a ready-made sample to copy and paste that fits your exact needs.

You can see post revisions for previous solutions but I think this is my favorite:

CREATE PROCEDURE dbo.DoThePivotThing
  @ProjectId int
AS
BEGIN
  DECLARE @cols nvarchar(max), 
          @agg  nvarchar(max),
          @sql  nvarchar(max);

  SELECT @cols = STUFF((SELECT ',
  '   QUOTENAME([Projected Month])
    FROM dbo.Projects WHERE [Project Id] = @ProjectId
    FOR XML PATH(''), TYPE).value
    (N'./text()[1]', N'nvarchar(max)'),1,1,'');

  SELECT @agg = STUFF((SELECT ',
  '   QUOTENAME([Projected Month]) 
      ' = MAX('   QUOTENAME([Projected Month])   N')'
    FROM dbo.Projects WHERE [Project Id] = @ProjectId
    FOR XML PATH(''), TYPE).value
    (N'./text()[1]', N'nvarchar(max)'),1,1,'');

  SET @sql = N';WITH x AS (
    SELECT * FROM dbo.Projects 
    CROSS APPLY (VALUES(1,''Labor'',Labor),
                   (2,''Equipment'',Equipment)) AS v(o,t,v)
    PIVOT (MAX(v) FOR [Projected Month] 
        IN ('   @cols   ')
    ) AS piv
  )
  SELECT Cost = t, '   @agg   N' FROM x GROUP BY t,o ORDER BY o;';

  EXEC sys.sp_executesql @sql, N'@ProjectId int', @ProjectId;
END

EXEC dbo.DoThePivotThing @ProjectId = 1;

EXEC dbo.DoThePivotThing @ProjectId = 2;

• Example