CODEWITHSUNDEEP
sqlsql-serverms-accesspivot
user2393148
user2393148

Reputation: 21

Pivot a single row

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.

Upvotes: 2

Views: 11280

Answers (2)

Taryn
Taryn

Reputation: 247710

You should be able to use the following query in both SQL Server and MS Access. The UNION ALL takes the columns and converts the values into multiple rows:

select 'P1AVERAGE' as AverageName, P1AVERAGE as AverageScore
from yourtable
union all
select 'P2AVERAGE' as AverageName, P2AVERAGE as AverageScore
from yourtable
union all
select 'P3AVERAGE' as AverageName, P3AVERAGE as AverageScore
from yourtable

Upvotes: 2

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

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:

Upvotes: 7

Related Questions