How to do pivoting in conjunction with FOR XML PATH in SQL Server?

Question

I have an input as

DECLARE @T TABLE(SName VARCHAR(20), Subject VARCHAR(20), Marks INT, ExamDate  DATE)

INSERT INTO @T
SELECT 'A', 'Subject1', 77, '2011-01-01' UNION ALL
SELECT 'A', 'Subject2', 97, '2011-01-01' UNION ALL
SELECT 'B', 'Subject1', 80 ,'2012-04-01' UNION ALL
SELECT 'B', 'Subject2', 70, '2012-03-01' UNION ALL
SELECT 'C', 'Subject1', 44, '2011-01-01' UNION ALL
SELECT 'C', 'Subject2', 90, '2011-01-01' UNION ALL
SELECT 'D', 'Subject1', 79 ,'2012-04-01' UNION ALL
SELECT 'D', 'Subject2', 66, '2012-03-01'

SELECT X.*
FROM ( SELECT
        t.*
        ,Rn = DENSE_RANK() OVER(PARTITION BY t.Subject ORDER BY t.Marks DESC)
FROM @T t) X WHERE X.Rn = 3

Output:

SName   Subject   Marks ExamDate        Rn
A       Subject1    77  2011-01-01       3
B       Subject2    70  2012-03-01       3

I am looking for an output as

SName   Subject1    Subject2
-----   --------    --------
A       77          
B                   70  

That means , since we are finding the 3rd highest marks of the students, so which ever student has obtained the 3rd highest mark, should come in the final desired list.

Had there been a tie between student A and C (say) for subject 1 and student C has obtained the third highest marks in Subject3 (say), then the output will be

SName   Subject1    Subject2    Subject3
-----   --------    --------    ---------
A,C     77          
B                   70  
C                               78  

The DDL for the second one is as under

DECLARE @T TABLE(SName VARCHAR(20), Subject VARCHAR(20), Marks INT, ExamDate  DATE)

INSERT INTO @T
SELECT 'A', 'Subject1', 77, '2011-01-01' UNION ALL
SELECT 'A', 'Subject2', 97, '2011-01-01' UNION ALL
SELECT 'A', 'Subject3', 99, '2011-01-01' UNION ALL
SELECT 'B', 'Subject1', 80 ,'2012-04-01' UNION ALL
SELECT 'B', 'Subject2', 70, '2012-03-01' UNION ALL
SELECT 'B', 'Subject3', 88, '2012-03-01' UNION ALL
SELECT 'C', 'Subject1', 77, '2011-01-01' UNION ALL
SELECT 'C', 'Subject2', 90, '2011-01-01' UNION ALL
SELECT 'C', 'Subject3', 78, '2011-01-01' UNION ALL
SELECT 'D', 'Subject1', 79 ,'2012-04-01' UNION ALL
SELECT 'D', 'Subject2', 66, '2012-03-01' UNION ALL
SELECT 'D', 'Subject3', 77, '2012-03-01'


SELECT X.*
FROM ( SELECT
        t.*
        ,Rn = DENSE_RANK() OVER(PARTITION BY t.Subject ORDER BY t.Marks DESC)
FROM @T t) X WHERE X.Rn = 3

I think it should be done by using FOR XML PATH and PIVOT. But how?

Thanks in advance

Answer 1

As you mentioned you need to combine XML + STUFF with PIVOT:

DECLARE @rn INT = 3;

;WITH cte AS
(
  SELECT X.*
  FROM ( SELECT t.*
        ,Rn = DENSE_RANK() OVER(PARTITION BY t.Subject ORDER BY t.Marks DESC)
        FROM @T t) X WHERE X.Rn = @rn
), cte2 AS
(
  SELECT DISTINCT Subject, Marks,
        [SName] =  STUFF((SELECT ',' + SName
                   FROM cte c2
                   WHERE c1.Subject = c2.Subject
                   ORDER BY SName
                   FOR XML PATH('')),1,1,'')
  FROM cte c1
)
SELECT SName, [Subject1],[Subject2], [Subject3]
FROM cte2
PIVOT
(
  MAX(Marks)
  FOR Subject IN ([Subject1],[Subject2], [Subject3])
) AS piv;

LiveDemo

Output:

╔═══════╦══════════╦══════════╦══════════╗
║ SName ║ Subject1 ║ Subject2 ║ Subject3 ║
╠═══════╬══════════╬══════════╬══════════╣
║ A,C   ║       77 ║          ║          ║
║ B     ║          ║       70 ║          ║
║ C     ║          ║          ║       78 ║
╚═══════╩══════════╩══════════╩══════════╝