Unpivot pairs in sql server

Question

I have a table with following data structure and data .

declare @temp table 
 ( 

  Name nvarchar(500),
  Class1 decimal(18,2), 
  Class1P decimal(18,2),  
  Class1S decimal(18,2),
   Class2 decimal(18,2), 
  Class2P decimal(18,2),  
  Class2S decimal(18,2),
   Class3 decimal(18,2), 
  Class3P decimal(18,2),  
  Class3S decimal(18,2)
  )

INSERT INTO @temp 
  SELECT 'Rahul',101,102,103,201,202,203,301,302,303

Now currently its show me data in such form for a simple select statement.

but i want to show the data in such format . I have idea that i have to first unpivot data then need to pivot the data but facing issue with syntax of unpiovt and pivot.

Answer 1

You didn't specify what version of SQL Server you are using but since you need to unpivot pairs of columns, you can use CROSS APPLY.

select 
  t.Name,
  c.Class, 
  c.Class1P, 
  c.Class1S
from @temp t
cross apply
(
  values
    (Class1, Class1P, Class1S),
    (Class2, Class2P, Class3S),
    (Class3, Class3P, Class3S)
) c (Class, Class1P, Class1S);

See SQL Fiddle with Demo. The above uses the VALUES which was made available in SQL Server 2008, if you are using SQL Server 2005, then you can replace VALUES with SELECT...UNION ALL.