Rotating sql table

Question

my sql talble has the following structure

F1      F2     F3     F4     F5 
Group   1      2      3      4  
Design  5      6      7      8 

now i want to read this and return a query result as show below please help

F1      Value 
Group   1    
Group   2    
Group   3    
Group   4 
Design  5       
Design  6   
Design  7   
Design  8   

Answer 1

SQL 2005 Introduced a pivot and matching unpivot clause, and this looks like a prime case, if you're unable to fix the underlying schema

Answer 2

Have you looked into Reporting Services, to see if this can be done with a cross-tab report?

Answer 3

You could use the UNION operator:

SELECT F1, F2 AS VALUE FROM TABLE
UNION
SELECT F1, F3 AS VALUE FROM TABLE
UNION
SELECT F1, F4 AS VALUE FROM TABLE
UNION
SELECT F1, F5 AS VALUE FROM TABLE