Pivot a single row

Question

I'm kinda new to using pivot. How can I pivot this record from this:

P1AVERAGE    P2AVERAGE    P3AVERAGE   -> column name
1.25         1.50         1.75

to this:

AVERAGENAME    AVERAGESCORE
p1Average      1.25
p2Average      1.50
p3Average      1.75

I'm using both SQL Server and MS Access 2007.

Answer 1

What are you trying to do is UNPIVOT not PIVOT, which is making the columns back into rows, it is the opposite of pivot.

For SQL Server, use the UNPIVOT table operator:

SELECT *
FROM tablename AS t
UNPIVOT
(
  AVERAGESCORE
  FOR plaveragename IN([P1AVERAGE], [P2AVERAGE], [P3AVERAGE])
) AS u;

See it in action here:

• SQL Fiddle Demo