How to create 3 columns from 2 columns with SQL query?

Question

I have the following table, Table1:

Id, Speed, Power
1, 1000, 100
1, 2000, 150
1, 3000, 200
2, 1000, 125
2, 2000, 175
2, 3000, 225

I would like to know SQL syntax to return the following:

Speed, Power600V, Power750V
1000, 100, 125
2000, 150, 175
3000, 200, 225

I tried this:

SELECT Speed, Power as Power600V From Table1 WHERE Id=1
UNION
SELECT Speed, Power as Power750V From Table1 WHERE Id=2

It isn't returning the data the right way though.

score 0 · Answer 1 · answered Oct 12 '16 at 09:25

You need to use a pivot table to get colums out of rows for what you're after

select speed, idp.[1] as power600V,idp.[2] as power750V
from tableName PIVOT
(
MAX(power)
FOR id in([1],[2])
) as idp

will get you the results you're after

1 Answers1