Multiple PIVOTs SQL Server I have a TSQL query that uses PIVOT to create columns called Analyst and Supervisor from the ApprovalLevelName column.

This is the current output:



In my query, I have ApprovedTime column, and Status column (commented out in the query below).

I want to also PIVOT these so that they both appear for analyst and supervisor. 
i.e. AnalystStatus, SupervisorStatus, AnalystApprovedTime, and SupervisorApprovedTime.

Below is how I want it to look...



How can I do this? Or is it possible in SQL? I tried using APPLY and another PIVOT but no success.

Query:

SELECT PivotTbl.*
FROM
( 
    SELECT 
        Code
        ,Period
        ,Task
        ,ApprovalLevelName
        ,ApprovedBy
        ,CommentText
        ,CommentPostedBy
        --,ApprovedTime
        --,Status
    FROM [Tasks].[TaskRecordDetail] tr
) AS SourceTbl
PIVOT
(
    MAX(ApprovedBy)
    FOR ApprovalLevelName IN ([Analyst],[Supervisor])
) AS PivotTbl  


Here is how the data looks without a PIVOT:
If these are the only two ApprovalLevelName values that are possible, and ignoring for now what you want to do with comments (since both rows could have comments), here is an approach that doesn't use PIVOT explicitly but still gets the results you want.

Table and sample data:

CREATE TABLE #tr
(
    Code              char(4), 
    Period            datetime, 
    Taskname          varchar(32), 
    ApprovalLevelName varchar(32), 
    ApprovedBy        varchar(32),
    Status            varchar(32), 
    ApprovedTime      datetime
);

INSERT #tr VALUES . -- please use unambiguous date formats
('RTX4','20180331','Test1','Analyst',    'user1','Approved','20180406'), 
('RTX4','20180331','Test1','Supervisor', 'user2','Approved','20180406'),
('RTX4','20180331','Test2','Analyst',     NULL,   NULL,      NULL),
('RTX4','20180331','Test2','Supervisor',  NULL,   NULL,      NULL); 


Query:

;WITH x AS 
(
  SELECT *, dr = CASE ApprovalLevelName WHEN 'Analyst' THEN 1 ELSE 2 END
  FROM #tr WHERE ApprovalLevelName IN ('Analyst','Supervisor')
)
SELECT Code, Period, Taskname,
  Analyst    = MAX(CASE dr WHEN 1 THEN ApprovedBy END),
  Supervisor = MAX(CASE dr WHEN 2 THEN ApprovedBy END),
  AnalystStatus    = MAX(CASE dr WHEN 1 THEN Status END),
  SupervisorStatus = MAX(CASE dr WHEN 2 THEN Status END),
  AnalystApprovedTime    = MAX(CASE dr WHEN 1 THEN ApprovedTime END),
  SupervisorApprovedTime = MAX(CASE dr WHEN 2 THEN ApprovedTime END)
FROM x
GROUP BY Code, Period, Taskname
ORDER BY Code, Period, Taskname;


Some repetition there, which goes up if you have more levels. Still, while PIVOT might seem simpler as a high level concept, it's also designed for simpler cases and when all of the possible output values are known, and this solution is more scalable in that way (and performance should be similar).

Results of the query seem to match your requirements:


  


Don't forget to clean up:

DROP TABLE #tr;