tsql : Pivot a table with multiple measures

Question

This is the first time I am trying to pivot a table. I have managed to pivot the table with only one measure and failed to do it with multiple measures. Please can I get some advice? Below is the test data I have and I would like to see. Please note that I have around 20 to 30 measures.

Thanks in advance!

-- Test Data HAVE
CREATE TABLE #have
(Name VARCHAR(50),Subject Varchar(20), Marks1 INT,Marks2 INT, Result Varchar(20) )
GO

INSERT INTO #have VALUES('Jsmith','Maths',65,56,'Pass')
INSERT INTO #have VALUES('Jsmith','Science',42,72,'Failed')
GO

-- Test Data WANT
CREATE TABLE #want
(Name VARCHAR(50),Maths_Marks1 INT ,Maths_Marks2 INT,Science_Marks1 INT,Science_Marks2 INT,  Maths_Result Varchar(20), Science_Result Varchar(20))
GO

INSERT INTO #want VALUES('Jsmith',65,56,42,72,'Pass','Failed')
GO

select * from #have
select * from #want


-- Pivot table

SELECT Name , [Maths] AS [Maths_Marks1], [Science] AS [Science_Marks1] 
FROM 
(SELECT Name, Subject, Marks1 FROM #have) as SourceTable
PIVOT
(SUM(Marks1)
FOR Subject in ([Maths],[Science])
) as PivotTable1

 -- Also Tried..Unable to get it working
  SELECT Name , [Maths] AS [Maths_Marks1], [Science] AS [Science_Marks1] 
 FROM 
 (SELECT Name, Subject, Marks1 FROM #have) as SourceTable
 PIVOT
 (SUM(Marks1)
 FOR Subject in ([Maths])
  ) as PivotTable1
 (SELECT Name, Subject, Marks2 FROM #have) as SourceTable
PIVOT 
(SUM(Marks2)
FOR Subject in ([Science])
 ) as PivotTable2

Answer 1

This trick builds on Marks properties : they are integers , mark <1000, exactly 2 marks columns (Marks1, Marks2) are in the table.

CREATE TABLE #have
(Name VARCHAR(50),Subject Varchar(20), Marks1 INT,Marks2 INT )


INSERT INTO #have VALUES('Jsmith','Maths',65,56)
INSERT INTO #have VALUES('Jsmith','Science',42,72)

SELECT Name, [Maths]/1000 AS [Maths_Marks1],  [Maths]%1000 AS [Maths_Marks2], [Science]/1000 AS [Science_Marks1], [Science]%1000 AS [Science_Marks2] 
FROM (SELECT Name, Subject, 1000*Marks1 + Marks2 Marks FROM #have) t
PIVOT (SUM(Marks)FOR Subject in ([Maths],[Science]) ) as p1

Otherwise , generally do it with conditional aggregates

SELECT Name
  , max(case Subject when 'Maths' then Marks1 end ) AS [Maths_Marks1]
  , max(case Subject when 'Maths' then Marks2 end ) AS [Maths_Marks2] 
  , max(case Subject when 'Maths' then Result end ) AS [Maths_Result] 
  , max(case Subject when 'Science' then Marks1 end ) AS [Science_Marks1]
  , max(case Subject when 'Science' then Marks2 end ) AS [Science_Marks2] 
  , max(case Subject when 'Science' then Result end ) AS [Science_Result] 
FROM  #have
GROUP BY name

Answer 2

You can try with pivot as below:

Select [Name], Max(Maths_Marks1) as Maths_Marks1, Max(Maths_Marks2) as Maths_Marks2, max([Science_Marks1]) as [Science_Marks1]
            ,max([Science_Marks2]) as [Science_Marks2], max([Maths_Result]) as [Maths_Result], max([Science_Result]) as [Science_Result]
    from (
    Select [Name], [Subject] + '_Marks1' as [Subject1], [Subject] +'_Marks2' as [Subject2], Marks1, Marks2, Result, [Subject] +'_Result' as [SubjectRes] from #have ) a
    pivot (max(marks1) for [Subject1] in ([Maths_Marks1],[Science_Marks1])) p
    pivot (max(marks2) for [Subject2] in ([Maths_Marks2],[Science_Marks2])) p1
    pivot (max(Result) for [SubjectRes] in ([Maths_Result],[Science_Result])) p2
group by [Name]