CODEWITHSUNDEEP
sqlsql-serverunpivot
rahularyansharma
rahularyansharma

Reputation: 10775

Unpivot pairs in sql server

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.

simple select statement result

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.

expected result

Upvotes: 0

Views: 441

Answers (2)

Taryn
Taryn

Reputation: 247810

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.

Upvotes: 3

Eoin Campbell
Eoin Campbell

Reputation: 44298

No need to pivot. just union 3 seperate queries together...

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
INSERT INTO @temp 
  SELECT 'Eoin',701,702,703,801,802,803,901,902,903

SELECT Name, Class1 as Class, Class1P as ClassP, Class1S as ClassS FROM @temp
UNION
SELECT Name, Class2, Class2P, Class2S FROM @temp
UNION
SELECT Name, Class3, Class3P, Class3S FROM @temp

RETURNS the results.

Name                 Class                                   ClassP                                  ClassS
-------------------- --------------------------------------- --------------------------------------- ---------------------------------------
Eoin                 701.00                                  702.00                                  703.00
Eoin                 801.00                                  802.00                                  803.00
Eoin                 901.00                                  902.00                                  903.00
Rahul                101.00                                  102.00                                  103.00
Rahul                201.00                                  202.00                                  203.00
Rahul                301.00                                  302.00                                  303.00

Upvotes: 1

Related Questions